List:General Discussion« Previous MessageNext Message »
From:Balazs Rauznitz Date:January 20 2004 7:24pm
Subject:Re: Select on indexed columns
View as plain text  
On Tue, Jan 20, 2004 at 08:23:32PM +0100, Aleksandar Bradaric wrote:
> Hi,
> 
> >> > Any way to make this faster ?
> >> 
> >> Try to create an index on both fields:
> >> 
> >>   create index idsex_index on sex (id, sex)
> 
> > Tried that; same results...
> 
> Could  you  post  the  result of the EXPLAIN command on that
> query?

mysql> explain  select count(*) from sex where id>459000 and id <=460000 and sex
> = 'M';
+-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
| table | type | possible_keys                      | key       | key_len | ref   | rows  
| Extra       |
+-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
| sex   | ref  | id_index,sex_index,sex,id,sex_both | sex_index |       2 | const | 506151
| Using where |
+-------+------+------------------------------------+-----------+---------+-------+--------+-------------+
1 row in set (0.00 sec)

mysql> desc sex;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  | MUL | NULL    |       |
| sex   | char(1) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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