Hacker News Reader

Comment by toast0 | Hacker News Reader

MySQL's query planner is actually pretty good, but there are some things that are just not going to be fast; most of which is documented, almost all of which can be seen with describe select ...; if it uses a temporary table, it's probably necessary, and it's definitely going to be slow once you have enough rows. If WordPress uses any of these things, it's not MySQL's fault.

View on HN

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.

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-... [2] https://kb.askmonty.org/en/subquery-optimizations-map/

Its query planner may be good (I don't know about this), in the sense that it can typically choose the best of a bad lot. However, the query executor (of stock MySQL) is awful - no hash joins? No merge joins? It is good compared to what?

Reply on news.ycombinator.com