List:General Discussion« Previous MessageNext Message »
From:Morten Date:July 21 2009 9:52am
Subject:Index selection problem
View as plain text  
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


Thread
Index selection problemMorten21 Jul
  • Re: Index selection problemJohnny Withers21 Jul
    • Re: Index selection problemMorten Primdahl21 Jul
      • Re: Index selection problemJohn Daisley21 Jul
        • Re: Index selection problemJohnny Withers23 Jul
  • Re: Index selection problemBrent Baisley21 Jul
    • Re: Index selection problemMorten Primdahl21 Jul