List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:July 21 2009 1:54pm
Subject:Re: Index selection problem
View as plain text  
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