Already did "Analyze table" and table is up to date. It seems like optimizer glitch only but I am not sure. Will try adding index hints.
From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Monday, December 14, 2009 9:14 PM To: Manish Ranjan Cc: mysql@lists.mysql.com Subject: Re: different type column and keys for EXPLAIN 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
