Common Table Expressions and Window Functions simple, maintainable, fast queries
MariaDB Server 10.2 has brought two new important querying features, Common Table Expressions (CTEs) and Window Functions. Both features provide, greater expressibility to queries, which leads to more opportunities for the optimiser to provide speedups With a focus on analytics, this talk will go through a number of examples on how to transform long complicated queries into simple, maintainable ones. With smart use of window functions, we will see how we can gain an order of magnitude speedup in some scenarios but also how to handle tricky use cases, which would otherwise result in long, complicated and brittle SQL queries.
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.