We're sorry but this page doesn't work properly without JavaScript enabled. Please enable it to continue.
Feedback

Boosting Performance of Order by Limit Queries

Formal Metadata

Title
Boosting Performance of Order by Limit Queries
Alternative Title
Knocking down the barriers of ORDER BY LIMIT queries with MariaDB 10.5
Title of Series
Number of Parts
490
Author
License
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Identifiers
Publisher
Release Date
Language

Content Metadata

Subject Area
Genre
Abstract
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.