Knocking down the barriers of ORDER BY LIMIT queries with MariaDB 10.5
- Track: MySQL, MariaDB and Friends devroom
- Room: H.2214
- Day: Saturday
- Start: 15:40
- End: 16:00
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.
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.
FULL DESCRIPTION:
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)
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
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
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.
Speakers
| Varun Gupta |