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.li...@mac.com> 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=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
