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
