Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes ! This is not a new problem: bugs about this can be traced back to 2014, and a blog post on this subject was published in 2015. But even if this is old news, because this problem recently came yet again to my attention, and because this is still not fixed in MySQL 5.7 and 8.0, this is a subject worth writing about.
The MySQL Optimizer
Before looking at the problematic query, we have to say a few words about the optimizer. The Query Optimizer is the part of query execution that chooses the query plan. A Query Execution Plan is the way MySQL chooses to execute a specific query. It …
[Read more]