List:General Discussion« Previous MessageNext Message »
From:Morten Primdahl Date:July 21 2009 5:43pm
Subject:Re: Index selection problem
View as plain text  
The other index does have a way higher cardinality, but the query is  
for 3 columns all of which are in the first index. I guess this is  
just one of the situations where MySQL makes a wrong assessment.


On Jul 21, 2009, at 3:54 PM, Brent Baisley wrote:

> Try doing a "SHOW INDEX FROM orders" and look at the cardinality
> column. These are the stats MySQL uses to determine which index to
> use. Sometimes they aren't always update properly and you may need to
> run ANALYZE on the table.
>
> But, you can also tell MySQL to use the index you want.
> SELECT * FROM orders USE INDEX (index_a) WHERE ...
>
> Brent Baisley
>
> On Tue, Jul 21, 2009 at 5:52 AM, Morten<my.lists@stripped> wrote:
>>
>> 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=1
>>
>>

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