MySQL Forums
Forum List  »  Optimizer & Parser

Re: 'where' with 'in' doesn't use index?
Posted by: Vlado Kurelec
Date: February 01, 2006 06:24AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
2848
January 24, 2006 05:25AM
Re: 'where' with 'in' doesn't use index?
2041
February 01, 2006 06:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.