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
-----
