http://dom.as/2015/07/30/on-order-by-optimization/
An insightful exploration by Domas (Facebook) on how some of the MySQL optimiser’s decision logic is sometimes naive, in this case regarding ORDER BY optimisation.
Quite often, “simple” logic can work better than complex logic as chasing all the corner cases can just make things worse – but sometimes, logic can be too simple.
Everything must be made as simple as possible, but no
simpler.
— Albert Einstein / Roger Sessions
MySQL instance running an Infobright engine
mysql> explain SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL
a;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | ac | ALL | NULL | NULL | NULL | NULL | 124426 |
|
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7594256 |
|
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
2 rows in set (0.01 sec)
mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON
ac.UID=a.UID;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 17466 |
+---------------+
1 row in set (0.23 sec)
…
[Read more]