Bartosz, You are right this is due to an optimizer inefficiency. But till the time the optimizer is not efficient enough we would have to use workarounds. In fact hints tend to be used a lot because although mostly optimizer makes good choices but sometimes it doesn’t.

… the performance in case of particular queries but surely cannot be an all-around answer to inefficiencies of the optimizer, that should be doing a good job in the first place. In addition, hints need to be… (application’s) logic which is simply a nightmare for developers in case of migration or major change…

… configuration, queries and hardware, even though fixing these does help in many cases. Whatever powerful and well tuned system you have…. Introduce Throttling Sometimes even single process overloads system too much in this case throttling by having relatively short queries and introducing…

… 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used… backward compatibility. In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are…

…? At the time when I implemented the so-called “greedy optimizer“, I did some experiments to determine what is a reasonable… depth (and the total number of tables) that can be optimized in reasonable time. I reached the conclusion that if we want… a conservative limit that guarantees that any query can be optimized quickly no matter how bad the pruning heuristics works. With…

… point of my blog post is what default value of optimizer_search_depth is impractical as 62! which is potential number… huge and it needs to be lowered down significantly in the cases optimizer spends too much time choosing the plan. I’ve looked at playing with optimizer_prune_level variable which is another one available. The default…