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:

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.

See Also

• Blog post MariaDB 10.1: Better query optimization for ORDER BY … LIMIT