Bug #63167 MAYBE a not optimized IN operation
Submitted: 9 Nov 2011 18:17 Modified: 11 Nov 2011 15:18
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: qc

[9 Nov 2011 18:17] Roberto Spadim 
Description:
hi, i´m executing a very big WHERE clause
but it´s a where in primary fields
in other worlds the number of IN rows, is the number of rows that must be founded

i don´t know if it´s a problem at optimizer, or query rewriter
could you help me optimizing this query?

How to repeat:
i have a very big table with about 15.000.000 rows, and i´m running a where (primary_key_field1,primary_key_field2,....) IN (
('row1-1','rows1-2',....'row-1-n'),
('row2-1','rows2-2',....'row-2-n'),
...
('rowm-1','rowsm-2',....'row-m-n'),

)

n=7, m=700

m=700 in other words, i should just find 700 rows
i will attach create table and select
[11 Nov 2011 8:13] Valeriy Kravchuk 
OK, you had sent table structure and SELECT. But what is the real problem? You get result that is incorrect or non-optimal plan is produced?
[11 Nov 2011 12:00] Roberto Spadim 
not optimal result plan

it get a full table scan, instead of index scan
i think (must test) that for more than X rows inside IN () it convert a index scan to a table scan, i will test some results to know what is this size
[11 Nov 2011 12:08] Roberto Spadim 
hummm
check...
explain
select * from est_mov where

(unidade_id,lote_tipo,lote_spa,item_id,item_id_red,mov_id,oe_tipo,oe,oe_seq,table_partition) in (
("1001","i","0.0","67","105","0010547878192","tr","43850","1","0"),
("1001","i","0.0","67","105","0010547978977","tr","43821","1","0")
)
=====
Using where,15634311 (rows),SIMPLE (select type),est_mov,ALL (type)

explain
select * from est_mov where

(unidade_id,lote_tipo,lote_spa,item_id,item_id_red,mov_id,oe_tipo,oe,oe_seq,table_partition) in (
("1001","i","0.0","67","105","0010547878192","tr","43850","1","0")
)
=====
SIMPLE (select type),est_mov,const (type),PRIMARY,63(key length),(const,const,const,const,const,const,const,const,const,const),1 (rows)

well i think IN () with 1 row, is the same as =(), and IN () with more than 1 rows, isn´t optimized as "((fields)=(values) or (fields)=(values) or (fields)=(values))" (maybe because partitions?)
[11 Nov 2011 12:13] Roberto Spadim 
this (or rewrited) is more optimized:

explain

select * from est_mov where
(unidade_id,lote_tipo,lote_spa,item_id,item_id_red,mov_id,oe_tipo,oe,oe_seq,table_partition) = 
("1001","i","0.0","67","105","0010547878192","tr","43850","1","0") or 
(unidade_id,lote_tipo,lote_spa,item_id,item_id_red,mov_id,oe_tipo,oe,oe_seq,table_partition) = 
("1001","i","0.0","67","105","0010547978977","tr","43821","1","0")

====
SIMPLE(select_type),est_mov,range(type),primary(key),63(key length), ref=null, rows=2, extra = using where
[11 Nov 2011 12:34] Roberto Spadim 
hi i rewrote with the OR clause, check the results:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra

1,SIMPLE,est_mov,range,PRIMARY,lote,oe,item,ponto_movimentacao,rastro_mov_id,transferencias,estoque,giro,rastro_mov_id,30,NULL,1828,Using where

rows :1828,Using where
very better, that´s what i want, but IN () can´t optimize like many ORs
[11 Nov 2011 15:08] Valeriy Kravchuk 
How is this different from reports like bug #31188 or bug #46288 then? I think this is a 100% duplicate of bug #31188.
[11 Nov 2011 15:16] Roberto Spadim 
yes duplicated, could you mark it?
thanks
[11 Nov 2011 15:18] Valeriy Kravchuk 
Duplicate of bug #31188.