Chuck Gadd said:
> Balazs Rauznitz wrote:
>> 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 ?
> Well, MySql can only use 1 index per table to optimize a query.
> It's apparently using the index on ID, so it then needs to examine
> all records in the right ID range to see if they meet the
> sex='M' condition.
> You could build an index on both fields as one index, and MySql
> should be able to use it to resolve both parts of the query.
> create index id_and_sex_index on sex (id,sex);
Does that really matter? We are talking about 5 byte rows (+
overhead). What is the minimum size you get back from a disk read? 512
bytes? 8192 bytes? How many records will there be in 1 disk read? What
is the chance that all records are M or F and thus the read would not
RAM might be cheap nowadays, but wouldn't you loose more by crowding
out the key bufer as you gain by reducing in-memory comparisons (I
seriously doubt it will save you any disk I/O)?