Re: Using keys and left()

[Alexander Newald]

----- Original Message ----- 
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 23, 2004 5:27 PM
Subject: Re: Using keys and left()


"Alexander Newald" <[EMAIL PROTECTED]> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]