Video details

Knocking down the barriers of ORDER BY LIMIT queries with MariaDB 10.5

Other
07.14.2020
English

by Varun Gupta
At: FOSDEM 2020 https://video.fosdem.org/2020/H.2214/mariadb_order_by_limit.webm
❮p❯The talk will start with a recap of how MariaDB(or MySQL) handles the ORDER BY LIMIT optimization and examples demonstrating why the current optimizer is not good enough.❮/p❯
❮p❯Further, the talk will describe how the optimizer in MariaDB 10.5 mostly solves the issue, the remaining unresolved issues and how DBAs can tackle them.❮/p❯❮p❯FULL DESCRIPTION:❮/p❯
❮p❯For the first part of the talk, I will discuss the possible strategies by which ORDER BY LIMIT optimization is handled in MariaDB (or MySQL)❮/p❯
❮p❯The strategies are: 1) Using an ordered index (ref, range or index scan) 2) Using filesort on the first non-const table 3) Using filesort on the temporary table, that stores the output of the join❮/p❯
❮p❯Then I will discuss how the current MariaDB/MySQL optimizer makes the choice between the strategies and show the situations where it will never get a good query plan❮/p❯
❮p❯For the second part of the talk, I will describe how a new cost-based optimization in MariaDB 10.5 solves the above issue. The talk will contain details about how the costs were taken into account during the optimization phase. Further, with the help of examples I would demonstrate how the execution differs for this new optimization and how this leads to improved performance for ORDER BY LIMIT queries.❮/p❯
Room: H.2214 Scheduled start: 2020-02-01 14:40:00