Heh. Try running "analyze table", so the index stats are correct. If that doesn't help, you may have stumbled upon an optimizer glitch, or maybe there's something happening that I'm not seeing. If all else fails, try to add index hints.
On Mon, Dec 14, 2009 at 4:30 PM, Manish Ranjan <manish.ran...@stigasoft.com>wrote: > Thanks Johan. > > More to add here, execution time for query with 'clarke' is much higher > than > query with 'clark'. This is why it looks a bit strange behavior. > > > > From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De > Meersman > Sent: Monday, December 14, 2009 8:33 PM > To: Manish Ranjan > Cc: mysql@lists.mysql.com > Subject: Re: different type column and keys for EXPLAIN > > > > 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 > > > >
