Optimizing Queries Using Window Functions
Window functions are a new feature of MariaDB 10.2. Window functions are a strong tool for solving analytic queries. They can allow for concise and clearer SQL code as well as faster running queries compared to regular SQL. This talk will go through an introduction of what window functions are, then focus on how various problems can be solved differently using window functions. We will see how long running queries can be turned from quadratic complexity to linear. The talk will also address maintainability of SQL code, given how window functions express computations. Sample problems we will be tackling: Computing the median of a set, smoothed averages, running totals, time series analysis, generating availability reports and others. All proposed approaches will be thoroughly benchmarked agains MariaDB and other DBMS to see exactly how big of a performance improvement is achieved.
Software Engineer, MariaDB Foundation
Started working for MariaDB as a Google Summer of Code student in 2013, implementing SQL Standard Roles. Later, converted to a full time employee as part of the MariaDB Foundation. Currently developing Window Functions for MariaDB. Also handling problems related to the Query Optimizer, Packaging and Replication.