MySQL Forums
Forum List  »  Optimizer & Parser

Need help with getting rid of filesort
Posted by: Marvin Herbold
Date: June 23, 2022 01:53PM

I have a pretty simple select query that the optimizer insists on using filesort for.

EXPLAIN SELECT
`id`, `displayName`, `order`
FROM
`someTable`
WHERE
`id` != 0
ORDER BY
`displayName`, `order`
LIMIT
123456, 1000

`id` is PK.

`displayName` and `order` have their own indexes.

I also have a ( `displayName`, `order` ) index as well as a ( `displayName`, `order`, `id` ) index.

The optimizer still insists on using filesort. If I add a FORCE INDEX to force it to use the ( `displayName`, `order` ) index, it works and runs much faster (50x faster). If I remove the WHERE clause, the filesort goes away. How can I properly set up this table / indexes so that I can have that where clause, and the optimizer would not use filesort?

Thanks for any help!

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help with getting rid of filesort
397
June 23, 2022 01:53PM


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.