MySQL Forums
Forum List  »  Optimizer & Parser

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

In your min example you use "WHERE col2 = xxx AND col1 = xxx" but your index is (col1, col2) and not (col2, col1).

Try to rewrite your query so it is something like:

SELECT MIN(recordNumber) FROM oaa_account WHERE recordNumber > 66728314 AND recordType = 0;

Also run an EXPLAIN SELECT on both your previous min query and the one I wrote above and paste the results in this forumthread.

The number of rows examined might be because you have that many rows which has recordNumber > 66728314.

Try a:

SELECT COUNT(*) FROM oaa_account WHERE recordNumber > 66728314;

vs your previous query (but modified for count()):

SELECT COUNT(*) FROM oaa_account WHERE recordType = 0 AND recordNumber
> 66728314;

along with EXPLAIN SELECT on both above queries.

Options: ReplyQuote

Written By
March 01, 2006 01:58PM
Re: MIN on an indexed field too slow
March 01, 2006 03:25PM
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.