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 be necessary? 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)? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
