Hi, I have a table "orders" with the columns
item_id INT FK items(id)
customer_id INT FK customers(id)
status_id TINYINT -- Between 1 and 4 always
ordered_at DATETIME
delivered_at DATETIME
There are indexes:
index_a: (item_id, customer_id, status_id)
index_b: (item_id, status_id, ordered_at, delivered_at)
Given this query:
SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531
AND status_id IN (1,2)
Then the key chosen is index_b. Same happens if I use (status_id = 1
OR status_id = 2). If I only check against one status_id, then the
"correct" index_a gets picked with ref const,const,const.
I'm not even doing a range scan on status_id and even if I were, it's
the last column in index_a. Since ordered_at and delivered_at are both
dates then index_b will have a very high selectivity. In reality,
index_b may make little sense, but I still don't understand why MySQL
would ever pick that when 3 columns in the query can use the covering
index_a
Can anyone give me some input on how to make sense of this?
Thanks,
Morten
select count(*) from orders where item_id = 9602 -> 4534 records
select count(*) from orders where item_id = 9602 and status_id IN
(1,2) -> 4181 records
select count(*) from orders where item_id = 9602 and customer_id =
5531 -> 1226 records
select count(*) from orders where item_id = 9602 and customer_id =
5531 and status_id IN (1,2) -> 1174 records
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
