Re: 'where' with 'in' doesn't use index?
CREATE TABLE `munia` (
`RecID` int(11) NOT NULL,
`RBr` smallint(6) NOT NULL,
`OzPo` smallint(6) NOT NULL,
`OzPP` char(1) collate cp1250_croatian_ci NOT NULL,
`RBrP` smallint(6) default NULL,
`I1` char(1) collate cp1250_croatian_ci default NULL,
`I2` char(1) collate cp1250_croatian_ci default NULL,
`Value` varchar(60) collate cp1250_croatian_ci default NULL,
`UValue` varchar(60) collate cp1250_croatian_ci default NULL,
PRIMARY KEY (`RecID`,`RBr`), --->888379 rows
KEY `MUniA01` (`OzPo`,`OzPP`,`Value`), --->214142 distinct values (127 rows for given query)
KEY `MUniA02` (`OzPP`,`Value`),
KEY `MUniA03` (`UValue`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci MIN_ROWS=10000 MAX_ROWS=1200000 AVG_ROW_LENGTH=70;
CREATE TABLE `munib` (
`RecID` int(11) NOT NULL,
`RBr` smallint(6) NOT NULL,
`OzPo` smallint(6) NOT NULL,
`OzPP` char(1) collate cp1250_croatian_ci NOT NULL,
`RBrP` smallint(6) default NULL,
`I1` char(1) collate cp1250_croatian_ci default NULL,
`I2` char(1) collate cp1250_croatian_ci default NULL,
`Value` varchar(60) collate cp1250_croatian_ci default NULL,
`UValue` varchar(60) collate cp1250_croatian_ci default NULL,
PRIMARY KEY (`RecID`,`RBr`), --->3494694 rows
KEY `MUniB01` (`OzPo`,`OzPP`,`Value`),
KEY `MUniB02` (`OzPo`,`OzPP`,`UValue`),
KEY `MUniB03` (`OzPP`,`Value`) --->887429 distinct values
) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_croatian_ci MIN_ROWS=1000000 MAX_ROWS=4000000 AVG_ROW_LENGTH=70;
query examples:
a) ----------------------------------------------------------------------------------------------------------
select * from plib.munib where ozpp='3' and value=
(select min(concat('001',recid)) from plib.munia where ozpo=200 and ozpp='a' and value like 'Ant%')
1 PRIMARY munib range MUniB03 MUniB03 64 1 Using where
2 SUBQUERY munia range MUniA01,MUniA02 MUniA01 66 127 Using where; Using index
1 row
0 secs
b) ----------------------------------------------------------------------------------------------------------
select * from plib.munib where ozpp='3' and value in
(select min(concat('001',recid)) from plib.munia where ozpo=200 and ozpp='a' and value like 'Ant%')
1 PRIMARY munib ref MUniB03 MUniB03 1 const 439842 Using where
2 DEPENDENT SUBQUERY munia range MUniA01,MUniA02 MUniA01 66 127 Using where; Using index
1 row
33 secs
c) ----------------------------------------------------------------------------------------------------------
select * from plib.munib where ozpp='3' and value in
(select concat('001',recid) from plib.munia where ozpo=200 and ozpp='a' and value like 'Ant%')
1 PRIMARY munib ref MUniB03 MUniB03 1 const 439842 Using where
2 DEPENDENT SUBQUERY munia range MUniA01,MUniA02 MUniA01 66 127 Using where; Using index
428 rows
53 secs
-------------------------------------------------------------------------------------------------------------
- all queries use MUNIB03 key but only a) uses both segments
- munib has:
- 3494694 rows
- 887429 distinct (ozpp,value) pairs
- 215500 rows that have ozpp='3'
- 77717 distinct values that have ozpp='3'
I also found an interesting thing regarding implicit type conversion:
select * from plib.munib where ozpp='3' and value='111000110'
1 SIMPLE munib ref MUniB03 MUniB03 64 const,const 1 Using where
select * from plib.munib where ozpp='3' and value=111000110
1 SIMPLE munib ref MUniB03 MUniB03 1 <<???>> const 439842 Using where
it seems that optimizer converts left side to int (so it can't use index)
instead of converting right side to char
regards, Vlado