List:General Discussion« Previous MessageNext Message »
From:Cabbar Duzayak Date:February 13 2012 9:13am
Subject:Re: Indexed Query examining too many rows!
View as plain text  
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
>
> -----
>
>>
>
Thread
Indexed Query examining too many rows!Cabbar Duzayak12 Feb
  • Re: Indexed Query examining too many rows!Reindl Harald12 Feb
    • Re: Indexed Query examining too many rows!Peter Brawley12 Feb
      • Re: Indexed Query examining too many rows!Cabbar Duzayak13 Feb