Hence DROP the smaller index when it is an exact prefix of the larger. New comment: When you have both INDEX(a) and INDEX(a,b), the Optimizer sometimes picks the shorter (1-col) on even though the larger (2-col) would be better.In general, you should not have both only have the larger index. INDEX(a,b) is bulkier than INDEX(a) a possible rational for using the latter is that it might entail fewer index blocks.Without the SELECT to look at, all I can do is hand-waving. I would expect the optimizer to choose i2 over author_id, but if I get it right it depends on the order they are defined.Īre these observations true in general or does it only happen for small tables? With the following indexes and a query where predicates involve author_id, country, org, publish_date, price: KEY `author_id` (`author_id`), Now all the sudden index i2 were chosen: table type possible_keys keyĭo I miss something or does the optimizer pick the first declared index that it benefits from even though there are better indexes declared later on? Why did it not choose i2 over i0 when it did choose author_id before? I got even more puzzled when I switched places in the table for i0 and i2: PRIMARY KEY (`id`), I was a bit surprised to see the new plan: table type possible_keys key So I removed the index author_id and created a new index like: KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`) table type possible_keys keyīook ref author_id,publish_date,i0,i1 author_id However, that did not help, the execution plan remained the same. In an answer given the query were rewritten as a LEFT JOIN, and that was the same thought that I had as well. ![]() The OP wanted to know if there were a way to optimize his query. Optimize a query with two range conditions
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |