List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:July 21 2009 1:27pm
Subject:Re: Index selection problem
View as plain text  
MySQL is unable to use your index when you use IN and/or OR on yoru column.

If the query is slow, you should switch to a union:

SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id =1
UNION
 SELECT * FROM orders WHERE item_id = 9602 AND customer_id = 5531 AND
status_id =2




On Tue, Jul 21, 2009 at 4: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
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

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