Speeding Up Analytical Queries in MariaDB
CTE's and Window Functions Highlight
Window functions are an important tool for analytical queries. Window functions act as regular functions but have access to more than one row during computation. This makes them highly useful for optimising queries involving self-joins, as well as queries that need to extract data from multiple related rows from the same table. The performance speedups obtained are sometimes 100 fold. This talk will explain how that is possible, as well as use cases for window functions.
On top of that, window functions pair well with another feature that MariaDB 10.2 has introduced - Common Table Expressions. This feature was contributed by a community member and helps make queries more maintainable and sometimes faster. This feature will be covered, mostly through the use of window functions, but also introduce new use cases.
The agenda for the talk is:
- What are CTEs
- What are window functions
- Solving problems with regular SQL and window functions
- Smoothing data
- Top-N Queries
- Difference between consecutive rows