3 ms·

This may have been true ten years ago. Not anymore. Mysql query planner is dumb as a rock. Easy example: instead of referring to a table in FROM, try using a su

This may have been true ten years ago. Not anymore. Mysql query planner is dumb as a rock. Easy example: instead of referring to a table in FROM, try using a sub query with SELECT * from the same table. It's an easy reproduction of the planner failing to process the query tree. It should result in the same execution tree. It results in a temporary table being created, as an identical copy of the original table. Before you attack the query itself, and note that it is an example.

michaelmior 14y ago
Fortunately there are big improvements coming to the specific case of subqueries in MySQL 5.6.3 [1]. MariaDB (a MySQL fork) also has several further optimizations [2]. [1] http://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-optimization.html http://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-... [2] https://kb.askmonty.org/en/subquery-optimizations-map/ https://kb.askmonty.org/en/subquery-optimizations-map/