From: Peter Brawley
Date: February 12 2012 10:57pm
Subject: Re: Indexed Query examining too many rows!
List-Archive: http://lists.mysql.com/mysql/226786
Message-Id: <4F3843CC.9030507@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------000700090205050006040409"

--------------000700090205050006040409
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2/12/2012 4:40 PM, Reindl Harald wrote:
> Am 12.02.2012 23:25, schrieb Cabbar Duzayak:
>> Hi All,
>>
>> I have a table with a btree index on its searchKey column, and when I
>> send a simple query on this table:
>>
>> explain select * from DataIndex where (searchKey like 'A%') order by
>> searchKey limit 10
>>
>> rows is returning 59548 and it tells me that it is using the searchKey index.
>>
>> Also, a select count(*) on this table returns 32104 rows, i.e.
>>
>> select count(*) from DataIndex where searchKey like 'a%' ->  gives
>> 32104 as its result
>>
>> Am I doing something wrong here? Given that the searched column is
>> indexed, shouldn't it examine way less rows?
> LIKE does not benefit from keys!

It does if the wildcard is not at the front, as indicated at 
http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/ 
...

*"When MySQL uses indexes*
...
When you use a LIKE that doesn't start with a wildcard.
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
..."

PB

-----

>

--------------000700090205050006040409--