The Ultimate Guide to High Availability with MariaDB

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).
  • 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).
  • 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.

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.