List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:December 14 2009 3:02pm
Subject:Re: different type column and keys for EXPLAIN
View as plain text  
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