----- Original Message -----
From: <Alec.Cawley@stripped>
To: <alexander@stripped>
Cc: <mysql@stripped>
Sent: Thursday, September 23, 2004 5:27 PM
Subject: Re: Using keys and left()
> "Alexander Newald" <alexander@stripped> wrote on 23/09/2004 15:57:51:
>
>> Hello,
>>
>> I have a db with abount 80000 lines in it. I now like to count the lines
>
>> where the first char of the id is "d":
>>
>> mysql> select count(id) from test where left(id,1) = "d";
>> +---------------+
>> | count(id) |
>> +---------------+
>> | 0 |
>> +---------------+
>> 1 row in set (1.83 sec)
>>
>> mysql> explain select count(id) from test where left(id,1) = "d";
>> +---------+-------+---------------+--------+---------+------+-------
>> +-------------------------+
>> | table | type | possible_keys | key | key_len | ref | rows |
> Extra
>> |
>> +---------+-------+---------------+--------+---------+------+-------
>> +-------------------------+
>> | test | index | NULL | id | 256 | NULL | 80352 |
> where
>> used; Using index |
>> +---------+-------+---------------+--------+---------+------+-------
>> +-------------------------+
>> 1 row in set (0.00 sec)
>>
>> mysql> select count(*) from test;
>> +----------+
>> | count(*) |
>> +----------+
>> | 80352 |
>> +----------+
>> 1 row in set (0.00 sec)
>>
>> What can I do to get a better result for my query?
>
> Does
> select count(id) from test where id like "d%" ;
> work any better? I would expect it to make better use of the index.
>
> Alec
>
Hello,
yes it works better for sets of data with very few lines with an id starting
with d. But it takes even longer when more lines are involved.
I thought using an index will "know" the result??
Alexander Newald
