MySQL Forums
Forum List  »  Optimizer & Parser

Re: MIN on an indexed field too slow
Posted by: Björn Steinbrink
Date: March 09, 2006 06:04AM

Sorting and MIN()/MAX() only works on the suffix on an index and filtering (WHERE foo = 123) only works on a prefix of an index. In your case, the query is exactly the opposite, regarding the existing key.

If possible (I don't know what else you do with that table), you could just switch the order of the parts of your primary key. Otherwise, add an extra index on (recordType, recordNumber). That should make the MIN() value be found instantly.

Edit: If you can't switch the order of the primary key parts and don't need instant but just faster results, an index on recordNumber alone should already speed up the query a lot, as MySQL can then use the index to access the rows in ascending order and stop on the first match for recordType.

Options: ReplyQuote


Subject
Views
Written By
Posted
4164
March 01, 2006 01:58PM
2297
March 01, 2006 03:25PM
2158
March 01, 2006 05:19PM
1979
March 02, 2006 03:28PM
Re: MIN on an indexed field too slow
2372
March 09, 2006 06:04AM


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.