Drop the 'sex_index'
Basically when you have few unique values in a column
indexing it doesn't always help.
I setup this table with the 1,000,000 random entries
and query time is nearly the same for your 2 queries
below.
CREATE TABLE sex (
id int(11) unsigned NOT NULL auto_increment,
sex char(1) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
You might also try setting 'sex' to ENUM('F','M')
and also try NOT NULL as well
if needed you could use 'U' for unknown
sex ENUM('F','M','U') NOT NULL default 'U'
olinux
--- Balazs Rauznitz <[EMAIL PROTECTED]> wrote:
>
> 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
>
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
