MySQL Forums
Forum List  »  Optimizer & Parser

How to optimize an ORDER BY query using a range to avoid any filesorts
Posted by: Faury Rodriguez
Date: October 14, 2007 12:57PM

I have the following query:
SELECT
series_id, weight
from series_main
WHERE
series_main.db_id IN (1,3,4,5,6,7,8,9,10,11,12,13,32,33,34,35,36,37,38,40,41,42)
order by weight
limit 5000;

I also have the following indices:
PRIMARY (series_id, weight)
weight (weight)
db_weight (db_id, weight)

The explain output of the above query is:
id: 1
select_type: SIMPLE
table: series_main
type: range
possible_keys: db_weight
key: db_weight
key_len: 1
ref: NULL
rows: 1896002
Extra: Using where; Using filesort

The query uses the db_weight index to look up the db_ids but doesn't use the weight to sort the results.

How can I optimize the appropriate index or perhaps the query to get MySQL to use the index to find the the rows with a db_id in my range but use the same index to then sort the results by weight and avoid any use of filesort?

Options: ReplyQuote




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.