Re: MIN on an indexed field too slow
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.
Subject
Views
Written By
Posted
4249
March 01, 2006 01:58PM
2336
March 01, 2006 03:25PM
2222
March 01, 2006 04:54PM
2200
March 01, 2006 05:19PM
2088
March 02, 2006 11:38AM
2019
March 02, 2006 03:28PM
Re: MIN on an indexed field too slow
2432
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.