List:General Discussion« Previous MessageNext Message »
From:Manish Ranjan Date:December 14 2009 3:30pm
Subject:RE: different type column and keys for EXPLAIN
View as plain text  
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: vegivamp@stripped [mailto:vegivamp@stripped] On Behalf Of Johan De
Meersman
Sent: Monday, December 14, 2009 8:33 PM
To: Manish Ranjan
Cc: mysql@stripped
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.ranjan@stripped>
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

 



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