I don't think there's an actual problem as such, the optimiser is just making a decision to merge the lastname and firstname indices for the second query. At a guess, I'd say that the cardinality of "clark" in your lastname index is too high, so it uses both; the cardinality for "clarke" will be lower, and probably low enough that using just the one index becomes faster.
No guarantees, though - that's just what it looks like from here. On Mon, Dec 14, 2009 at 1:03 PM, Manish Ranjan <manish.ran...@stigasoft.com>wrote: > I am using mysql 5.0.77 on RHEL 5. Storage engine in MyISAM. > > > > Please refer to the below two statements. First query is checking for > lastname 'clarke' where as second query is checking for lastname 'clark'. > Rest everything is same with these two queries. However, the explain output > shows "ref" for the first query and uses only one key for the first query > whereas second query uses "index_merge" and both keys. > > > > mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' > and > fldLASTNAME='clarke'; > > > +----+-------------+-------------------+------+--------------------------+-- > -----------+---------+-------+-------+-------------+ > > | id | select_type | table | type | possible_keys | > key | key_len | ref | rows | Extra | > > > +----+-------------+-------------------+------+--------------------------+-- > -----------+---------+-------+-------+-------------+ > > | 1 | SIMPLE | tblList | ref | fldLASTNAME,fldFIRSTNAME | > fldLASTNAME > | 31 | const | 35043 | Using where | > > > +----+-------------+-------------------+------+--------------------------+-- > -----------+---------+-------+-------+-------------+ > > 1 row in set (0.07 sec) > > > > mysql> explain select count(*) from tblList where fldFIRSTNAME='michael' > and > fldLASTNAME='clark'; > > > +----+-------------+-------------------+-------------+---------------------- > > ----+--------------------------+---------+------+------+-------------------- > -------------------------------------------------+ > > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | Extra > | > > > +----+-------------+-------------------+-------------+---------------------- > > ----+--------------------------+---------+------+------+-------------------- > -------------------------------------------------+ > > | 1 | SIMPLE | tblList | index_merge | fldLASTNAME,fldFIRSTNAME | > fldLASTNAME,fldFIRSTNAME | 31,31 | NULL | 2190 | Using > intersect(fldLASTNAME,fldFIRSTNAME); Using where; Using index | > > > +----+-------------+-------------------+-------------+---------------------- > > ----+--------------------------+---------+------+------+-------------------- > -------------------------------------------------+ > > 1 row in set (0.02 sec) > > > > What could be the problem here. Please help. > > > > Thanks, > > Manish > >
