List:General Discussion« Previous MessageNext Message »
From:Manish Ranjan Date:December 14 2009 12:03pm
Subject:different type column and keys for EXPLAIN
View as plain text  
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



Thread
different type column and keys for EXPLAINManish Ranjan14 Dec
Re: different type column and keys for EXPLAINJohan De Meersman14 Dec
  • RE: different type column and keys for EXPLAINManish Ranjan14 Dec
RE: different type column and keys for EXPLAINManish Ranjan14 Dec
Re: different type column and keys for EXPLAINJohan De Meersman14 Dec
Re: different type column and keys for EXPLAINSergey Petrunya15 Dec
  • RE: different type column and keys for EXPLAINManish Ranjan15 Dec