List:General Discussion« Previous MessageNext Message »
From:olinux Date:January 21 2004 10:27am
Subject:Re: Select on indexed columns
View as plain text  
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 <balazs@stripped> 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
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