In question:
Optimize a query with two range conditions
the OP wanted to know if there were a way to optimize his query. In an answer given the query were rewritten as a LEFT JOIN
, and that was the same thought that I had as well. However, that did not help, the execution plan remained the same.
table type possible_keys key
book ref author_id,publish_date,i0,i1 author_id
So I removed the index author_id and created a new index like:
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)
I was a bit surprised to see the new plan:
table type possible_keys key
book ref publish_date,i0,i1,i2 i0
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`),
KEY `publish_date` (`publish_date`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`),
KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)
Now all the sudden index i2 were chosen:
table type possible_keys key
book ref publish_date,i2,i0,i1 i2
Do 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?
With the following indexes and a query where predicates involve author_id, country, org, publish_date, price:
KEY `author_id` (`author_id`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)
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.
Are these observations true in general or does it only happen for small tables?