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
