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.

… cannot be an all-around answer to inefficiencies of the optimizer, that should be doing a good job in the first…

One of the very frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out what the cause is some batch jobs, reports and other non response time critical activities are overloading the system causing user experience to …

… was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need… making it unusable to check the optimizer performance. Solution for this problem was to use set optimizer_search_depth=0, rarely used… chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low…

…? 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… 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… 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…