List:General Discussion« Previous MessageNext Message »
From:Balazs Rauznitz Date:January 19 2004 9:42pm
Subject:Select on indexed columns
View as plain text  
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


Thread
Select on indexed columnsBalazs Rauznitz20 Jan
  • Re: Select on indexed columnsAleksandar Bradaric20 Jan
    • Re: Select on indexed columnsBalazs Rauznitz20 Jan
      • Re[2]: Select on indexed columnsAleksandar Bradaric20 Jan
        • Re: Select on indexed columnsBalazs Rauznitz20 Jan
          • Re: Select on indexed columnsDan Nelson20 Jan
  • Re: Select on indexed columnsolinux21 Jan