Improvements to ORDER BY Optimization
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
- The ORDER BY optimizer takes multiple-equalities into account (MDEV-8989). This optimization is not enabled by default in MariaDB 10.1. You need to explicitly switch it ON by setting the optimizer_switch system variable, as follows:
optimizer_switch='orderby_uses_equalities=on'
Setting the switch ON is considered safe. It is off by default in MariaDB 10.1 in order to avoid changing query plans in a stable release. It is on by default from MariaDB 10.2
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.
