Select on indexed columns

[Balazs Rauznitz]

While doing some benchmarks the other day, I saw surprisingly slow 
query results on columns that were indexed.

Here's the table definition:

create table sex (
id integer,
sex char(1));
create index id_index on sex (id);
create index sex_index on sex (sex);

Then I loaded a million rows, id was from 1 to 1_000_000, sex was
randomly 'F' or 'M'.

When searching on 'id' everything is snappy:

mysql> select count(*) from sex where id>459000 and id <=460000;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

However when the 'sex' column is involved:

mysql> select count(*) from sex where id>459000 and id <=460000 and sex = 'M';
+----------+
| count(*) |
+----------+
|      504 |
+----------+
1 row in set (5.09 sec)

Any way to make this faster ?

I'm using MySQL 4.0.16 on Linux 2.4.x with a 1GHz AMD CPU and 640M RAM.

<Insert jokes about sex making MySQL slow here>

Thanks,

Balazs