Re: Indexed Query examining too many rows!
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-2719/ ... *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
Re: Indexed Query examining too many rows!
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! signature.asc Description: OpenPGP digital signature
Re: Indexed Query examining too many rows!
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-2719/ ... *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 -
