Improvements to ORDER BY Optimization in MariaDB 10.1
MariaDB starting with 10.1
MariaDB 10.1 includes several improvements to the ORDER BY optimizer.
The fixes were made as a response to complaints by MariaDB customers, so they fix real-world optimization problems. The fixes are a bit hard to describe (as the ORDER BY
optimizer is complicated), but here's a short description:
The ORDER BY optimizer in MariaDB 10.1:
- Doesn’t make stupid choices when several multi-part keys and potential range accesses are present (MDEV-6402).
- This also fixes MySQL Bug#12113.
- Always uses “range” and (not full “index” scan) when it switches to an index to satisfy
ORDER BY … LIMIT
(MDEV-6657). - Tries hard to be smart and use cost/number of records estimates from other parts of the optimizer (MDEV-6384, MDEV-465).
- This change also fixes MySQL Bug#36817.
- Takes full advantage of InnoDB’s Extended Keys feature when checking if filesort() can be skipped (MDEV-6796).
Extra optimizations
Starting from MariaDB 10.1.15
- ORDER BY optimizer takes multiple-equalities into account (MDEV-8989). This optimization is not enabled by default. You need to explicitly switch it ON like so:
optimizer_switch='orderby_uses_equalities=on'
Setting the switch ON is considered safe, the reason it is not on by default is that the optimization was added in 10.1.15 and we wanted to avoid changing query plans in a stable release.
Comparison with MySQL 5.7
In MySQL 5.7 changelog, one can find this passage:
Make switching of index due to small limit cost-based (WL#6986) : We have made the decision in make_join_select() of whether to switch to a new index in order to support "ORDER BY ... LIMIT N" cost-based. This work fixes Bug#73837.
MariaDB is not using Oracle's fix (we believe make_join_select
is not the right place to do ORDER BY optimization), but the effect is the same: this case is covered by MariaDB 10.1's optimizer.