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