Need help with getting rid of filesort
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!
Subject
Views
Written By
Posted
Need help with getting rid of filesort
779
June 23, 2022 01:53PM
313
June 24, 2022 09:59AM
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.