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
On Mon, Feb 13, 2012 at 12:57 AM, Peter Brawley
> 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
>>> 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
> *"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%'