SQL Query Stuck in Statistics State

I ran into the same problem recently: MySQL started to choke (stuck in state 'statistics') on queries with a lot of tables joined together. I found a good blog post explaining why this happens and how to solve it.

Basically at least in MySQL 5.5, the default value for the configuration parameter optimizer_search_depth is 62 which will cause the query optimizer to take exponentially more time as the number of tables in the query increases. After a certain point it will start to take days or even longer to finish the query.

However, if you set the optimizer_search_depth to 0 in your my.cnf, MySQL automatically chooses suitable depth, but limits it to 7 which is not yet too complex.

Apparently this issue has been fixed in MySQL 5.6 but I haven't tested it myself yet.

I had the same issue on a Centos server 4 cores and 180GB ram. A simple query was taking forever to run and stuck on statistics state just like what you have explained. I ran OPTIMIZE TABLE on the tables it was querying and like 5 minutes later problem was solved. However, I never found out if it really solved the issue or not.

Just a suggestion.

I spent the day yesterday dealing with a similarly troublesome query...

Another option to quickly eliminate query optimizer hangs is to add STRAIGHT_JOIN just after SELECT.

For example:

SELECT STRAIGHT_JOINtable1.column_a,table1.column_b,table2.column_a,table2.column_bFROM table1JOIN table2 USING(column_a)

This is not an ideal solution, since it is entirely bypassing query optimization for joins, but if you're stuck solving a mission-critical problem in a legacy application like I was yesterday, it might help. I do not recommend slapping this on every query, but it's an effective solution in some cases.