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

  • ORDER BY optimizer takes multiple-equalities into account (MDEV-8989). This optimization is not enabled by default. 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 in 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...