A customer of ours had an interesting problem regarding a query that was taking too long, around 55s. Looking at the query with the query profiler we found that it was spending most of its time in the "statistics" phase. Now the query was pretty complex, it contained nearly 20 tables with INNER JOINs, LEFT JOINs and even some subqueries. However the tables were small and fetching all the data shouldn't have taken the 55 seconds the query was taking. The problem was that the optimiser was spending too much time evaluating and finding the optimal execution plan.

There are two options in MySQL with which you can control the optimiser's behaviour a bit. The first one is optimizer_prune_level. The pruner discards non-optimal execution plans early without evaluating them fully. It is turned on by default and is not recommended to turn off unless there's a really good reason. For testing purposes we turned the pruner off for this query, but …