Re: MIN on an indexed field too slow

[Apachez]

The explains shows that in this case there is no difference of using WHERE col2 = xxx AND col1 > xxx; vs WHERE col1 > xxx AND col2 = xxx; however there are situations where the query optimizer might fail with its "preprocessing" which means that it will default the query the one the client has sent to the server.

Try to change the > into >= and see if the explain will change somehow.

MySQL will examine all matching rows because you told it to examine all rows satisfying recordnumber > 66728314 which according to the explain means that it has to examine 189504 rows.

Which mysql version do you use and which settings for keybuff and readbuff, readrndbuff, sortbuff and joinbuff do you use ? The most important in this case should be the keybuff.

Also, how large is the index (if you use myisam you can check the filesize of the MYI file for the table in question) ?

Also try running an ANALYZE TABLE (which will resort the index) on the table or OPTIMIZE TABLE (which will both resort the index and "defragment" the datatable) and see if that somehow changes the output of the EXPLAIN SELECT's above ?

Another hint would be if you try adding a LIMIT 1 in the end of the query and see how EXPLAIN SELECT changes ?

Otherwise I agree with you, if the index is sorted (ANALYZE TABLE/OPTIMIZE TABLE) then the engine shouldnt have to process 189k rows since only the first ones matches the WHERE clause and the rest of the recordnumbers are higher then the (66728318, 0) match.