MySQL Forums
Forum List  »  Optimizer & Parser

Re: MIN on an indexed field too slow
Posted by: Apachez
Date: March 01, 2006 05:19PM

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.

Options: ReplyQuote

Written By
March 01, 2006 01:58PM
March 01, 2006 03:25PM
Re: MIN on an indexed field too slow
March 01, 2006 05:19PM
March 02, 2006 03:28PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.