Varun Gupta

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.