Hi,
As you can see in my query, % is not in the beginning. Once again, it is :
select * from DataIndex where (searchKey like 'A%') order by searchKey limit 10
where searchKey has a btree on it.
As Peter was saying, percent in the beginning does a full table scan
as expected.
Thanks.
On Mon, Feb 13, 2012 at 12:57 AM, Peter Brawley
<peter.brawley@stripped> wrote:
> 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
>
> -----
>
>>
>
