"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
