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.braw...@earthlink.net> 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 > > ----- > >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
