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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
