List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 11 2010 5:44pm
Subject:Re: Innodb Choosing Random Index
View as plain text  
In the last episode (Jul 11), Leonardo Leonardo said:
> On Sun, Jul 11, 2010 at 7:07 PM, Leonardo Leonardo
> <new2mysql9@stripped>wrote:
> > Here is the structure of the Table T1 ( ENGINE=InnoDB ) -
> >
> >   `c1` varchar(128) NOT NULL default '',
> >   `c2` int(11) NOT NULL default '0',
> >   `c3` varchar(32) NOT NULL default '',
> >   `c4` blob,
> >   `c5` double default NULL,
> >   `c6` varchar(255) default NULL,
> >   `c7` enum('BLOB','NUMERIC','STRING') NOT NULL default 'BLOB',
> >   KEY `key1` (`c1`,`c2`,`c3`,`c5`),
> >   KEY `key2` (`c1`,`c2`,`c3`,`c6`),
> >
> > The query is as below -
> > SELECT * FROM T1 WHERE (c1 = '$string1' AND c2 IN ($int1, $int2 , $int3,
> > $int4 , $int5, $int6 , $int7 , $int8, $int9, $int10 ))
> >
> > Mysql is choosing key1 & key2 in different situations .

InnoDB estimates index cardinality on every query by examining a few random
disk blocks in each index.  Depending on which pages are examined, mysql
might decide one index is better then the other even if everything else is
the same.  Try running EXPLAIN SELECT on the same query a few times and see
if the optimizer picks different indexes.  If you're running a new enough
version of mysql (5.1.38 or newer), you can change the
innodb_stats_sample_pages variable to raise the number of pages from the
default of 8 (try 16).  That will make the estimate more accurate and
hopefully mysql will pick the right index consistently.

 
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_sample_pages

Another solution might be to create another index on (c1,c2), since that
index is the most efficient one for your particular query.  MySQL will
always pick that index over the other two.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Innodb Choosing Random Indexarijit bhattacharyya11 Jul
  • Re: Innodb Choosing Random Indexarijit bhattacharyya11 Jul
    • Re: Innodb Choosing Random IndexPrabhat Kumar11 Jul
Re: Innodb Choosing Random IndexLeonardo Leonardo11 Jul
  • Re: Innodb Choosing Random IndexDan Nelson11 Jul