![]() The content in this blog is provided in good faith by members of the open source community. Well, this longer explanation has already been written by Domas Mituzas in 2015, so I am referring you to his on ORDER BY optimization post for more details. ![]() One last thing before ending this post: I wrote above that I would give a longer explanation about the reason for this bad choice by the optimizer. In that report, I mention a query that is taking 12 minutes because of a bad choice by the optimizer (when using the good plan, the query is taking less than 0.1 second). PS-4935 is a duplicate of PS-1653 that I opened a few months ago. #Mysql optimizer turn off full#PS-4935: Optimizer choosing full table scan (instead of index range scan) on query order by primary key with limit. ![]() PS-1653: Optimizer chooses wrong index for ORDER BY DESC.Bug#78612: Optimizer chooses wrong index for ORDER BY.Bug#74602: Optimizer prefers wrong index because of low_limit.I claim Bug#74602 is not fixed even if it is marked as such in the bug system, but I will not make too much noise about this as Bug#78612 also raises attention on this problem: Well, the best solution would be to fix the bugs below. Now the query is almost instant, but this is not my favourite solution because if we drop the index, or if we change its name, the query will fail. How can we solve this ? The first solution is to hint MySQL to use key1 as shown below. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table. ![]() What ? The query is not using the index key1, but is scanning the whole table (key: PRIMARY in above EXPLAIN) ! How can this be ? The short explanation is that the optimizer thinks - or should I say hopes - that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. Mysql> EXPLAIN SELECT * FROM _test_jfg_201907 The SHOW CREATE TABLE for our table is below. Now that we know what are the Query Optimizer and a Query Execution Plan, I can introduce you to the table we are querying. 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.īefore 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 includes index choices, join types, table query order, temporary table usage, sorting type … You can get the execution plan for a specific query using the EXPLAIN command. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |