Showing entries 1 to 2
Displaying posts with tag: optimiser (reset)
on ORDER BY optimization | Domas Mituzas

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 vs InfoBright optimizer battle

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]
Showing entries 1 to 2