List:General Discussion« Previous MessageNext Message »
From:Manish Ranjan Date:December 15 2009 8:50am
Subject:RE: different type column and keys for EXPLAIN
View as plain text  
Thanks Sergey.

The query is much slower with "ref". Do you think if a composite index on
firstname and lastname would solve it? Table has 164+ million records which
makes me reluctant to create a new index due to the time required for index
creation unless I am pretty sure that the new index would work. 


-----Original Message-----
From: Sergey Petrunya [mailto:psergey@stripped] 
Sent: Monday, December 14, 2009 5:58 PM
To: Manish Ranjan
Cc: mysql@stripped
Subject: Re: different type column and keys for EXPLAIN

Manish,

On Mon, Dec 14, 2009 at 05:33:43PM +0530, Manish Ranjan 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. 
> 
> ... 
> 
> What could be the problem here. Please help.

"ref" and "index_merge" are two possible plans for both of the queries. The
choice whether to use ref or index_merge depends on cost calculations,
which,
in turn, depends on estimates of numbers of records that one will get for
conditions in the WHERE clause. 

It seems that the storage engine reports different estimates for number of
matching records for lastname='clark' and lastname='clarke', and hence the
query plans are different. This is a normal situation.

Does that cause any problems for you? That is, do you observe that one of
the
queries is unccecessarily slow (i.e. much slower than you could make it to
run
by using some hint?)

BR
 Sergey
-- 
Sergey Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog

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