Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table structure as follows :

CREATE TABLE `sale_product_inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tier_number` int(11) NOT NULL DEFAULT '1',
  `sale_product_pool_id` int(11) DEFAULT NULL,
  `inventory` int(11) NOT NULL,
  `in_cart_units` int(11) DEFAULT '0',
  `size_display_order` tinyint(4) NOT NULL DEFAULT '0',
  `last_updated_by` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`sale_id`,`product_id`,`tier_number`,`size`,`sale_product_pool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

NOTE : I have an Index UNIQUE = sale_id,product_id,tier_number,size,sale_product_pool_id

When I run this query :

select * from sale_product_inventories 
where 
sale_id in (502,504)  and 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

Query Plan for the query above MySql Uses the index Unique and the execution time is 0.7 millisecond

BUT

when I run this query

select * from sale_product_inventories 
where 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

Query Plan for the second query

MySql does not use the UNIQUE index and the execution time is 76 millisecond.

Mysql : 5.5.27 InnoDB Version : 1.1.8

My Question is Why is mysql behaving in such a way. Can some one please help me with this.

EDIT :
I came across this so thought it might be useful to add MySQL generally can’t use indexes on columns unless the columns are isolated in the query. “Isolating” the column means it should not be part of an expression or be inside a function in the query.

share|improve this question
    
Can you try WHERE sale_id=502 AND (product_id=2 OR product_id=1 OR product_id=3 OR product_id=4) OR sale_id=504 AND (product_id=2 OR product_id=3) and tell us if the index is used? –  ypercube May 23 '13 at 16:03
    
And the join that Rolando suggests? It would be good if you added those EXPLAIN plans in the question, too. –  ypercube May 23 '13 at 16:10
    
@ypercube : Tried it. The Unique index is used with the where clause as provided by you. What are you trying to get at ? –  Egalitarian May 23 '13 at 18:29
    
Michael-sqlbot answered (with what I was trying to get at.) –  ypercube May 24 '13 at 10:43
    
Regarding your last comment. This is called "Sargability". See the related SO question: What makes a SQL statement sargable? For example YEAR(datecolumn)=2013 is not sargable but datecolumn >= '2013-01-01' AND datecolumn < '2014-01-01' is. –  ypercube Jun 1 '13 at 14:02
add comment

2 Answers

up vote 6 down vote accepted

The MySQL optimizer cannot optimize expressions in this format:

WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))

It's not a matter of getting the indexes right -- it appears that it's just not implemented.

The optimizer does not understand that this is equivalent to...

WHERE (col_1,col_2) IN ((a,b)) 
   OR (col_1,col_2) IN ((c,d)) 
   OR (col_1,col_2) IN ((e,f)) 

... or ...

WHERE (col_1 = a AND col_2 = b)
   OR (col_1 = c AND col_2 = d)
   OR (col_1 = e AND col_2 = f)

There's Bug #35819, which I originally found in this article, which was in turn mentioned in the comments on this post.

Unfortunately, I didn't find those until I had already broken out the new Optimizer Trace in MySQL 5.6 and run a few test cases through it. It seemed like a safe bet that if 5.6 couldn't handle it, then previous versions couldn't handle it.

It turns out that MySQL 5.6 indeed cannot handle it. The "set in set of sets" construct seems to simply not be something the optimizer catches on to. So in this case, it's not a matter of the optimizer choosing a full table scan over other plans -- the optimizer actually concludes that there aren't even any other possible plans to consider.

This is only true for multiple "row constructors" on the right hand side of IN. For a single expression, the optimizer does its thing and realizes this is equivalent to col_1 = a AND col_2 = b:

WHERE (col_1,col_2) IN ((a,b))    # is optimized correctly
WHERE (col_1,col_2) IN (ROW(a,b)) # is an equivalent expression in MySQL

Interestingly, your original EXPLAIN suggests the unique index was not being used in exactly the way that you may have believed it was being used, at any rate. It was only being used to find rows with the desired sale_id... not both values.

You'll notice in your original EXPLAIN that the key_len is shown as 4, meaning only the leftmost 4 bytes of the index are going to be examined -- sale_id, a 4-byte INT would be the leftmost 4 bytes in that index. The Using where means the optimizer realizes that additional filtering of the rows returned from the range scan may be needed to eliminate any rows that don't satisfy the rest of the WHERE clause -- all rows with sale_id 502 and 504 are being retrieved via the index regardless of their value for product_id, and then the resulting rows will be subsequently filtered to satisfy the additional constraints imposed by the WHERE.

The optimum path is probably to stick with (expr and expr) or (expr and expr) or (expr and expr) in your where clause. It's logically equivalent and the optimizer understands it.

Additional note, regarding some of your comments... in light of what I've discussed above, index hints will not help, because the optimizer appears unaware of the equivalence of the expression you've used to other expressions that it could handle... but as a point of reference, the reason it was syntactically invalid is that you have to use the name of the index, not the list of columns in the index. You've called your unique index 'UNIQUE', so the way to use this as an index hint would be in this format:

USE INDEX(`UNIQUE`)
share|improve this answer
add comment

Try refactoring the query as an all-out JOIN

SELECT B.* FROM
(
    SELECT 502 sale_id,2 product_id
    UNION SELECT 502,1
    UNION SELECT 502,3
    UNION SELECT 504,4
    UNION SELECT 504,2
    UNION SELECT 504,3
) A INNER JOIN sale_product_inventories B
USING (sale_id,product_id);

The index has to be utilized in this case.

Why did it not use the index with your original query? I blame it on the WHERE clause because the Query Optimizer saw sale_id lookup first and then probably decided that with the remainder of the WHERE clause that a full table scan was the path of least resistance.

Give it a Try !!!

share|improve this answer
    
Is the takeaway this. Even if you have an index (A,B,C) you should never use a where clause like (A,B,C) in ( (a1,b1,c1), (a2,b2,c2), (a3,b3,c3),(a4,b4,c4),(a5,b5,c5)) ?? –  Egalitarian May 23 '13 at 18:52
    
Yes, that's right. It would be nice if the Query Optimizer was smart enough to recognize the pattern and sculpt it internally into a JOIN, but that's just wishful thinking. –  RolandoMySQLDBA May 23 '13 at 18:58
    
Thanks. I tried this too : select * from sale_product_inventories where USE INDEX (sale_id,product_id,tier_number,size,sale_product_pool_id) (sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) ) so as to use query hint but it kept me giving this error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USE INDEX (sale_id,product_id,tier_number,size,sale_product_pool_id) (sale_id, p' at line 2. Do you have any idea what is wrong ? –  Egalitarian May 23 '13 at 19:07
    
Try putting the WHERE after USE INDEX and before (sale_id, product_id) in. –  RolandoMySQLDBA May 23 '13 at 19:08
    
This is weird : Doing this gives me select * from sale_product_inventories USE INDEX (sale_id,product_id,tier_number,size,sale_product_pool_id) where (sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) ) gives me this error now : Key 'sale_product_pool_id' doesn't exist in table 'sale_product_inventories' The schema structure is same as given above. –  Egalitarian May 23 '13 at 19:13
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.