Why is MySQL performing a filesort on this query?

Question

Why is MySQL performing a filesort on this query? I'm expecting simply Using where:

SELECT * FROM active_campaign_days
WHERE ad_unit_id=1
AND day='2017-12-10'
ORDER BY bid DESC, budget DESC, campaign_id ASC

Index on (ad_unit_id, day, bid, budget, campaign_id) - the database is using this index according to the EXPLAIN.

In this particular result set, bid and budget are the same for all rows. (ad_unit_id, campaign_id) is unique.

EXPLAIN:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  active_campaign_days    ref     ad_unit_id,ad_unit_id_2     ad_unit_id_2    7   const,const     5   Using where; Using filesort

Answer 1

Let's dissect the query..

1. The WHERE ad_unit_id=1 AND day='2017-12-10' needs an index starting with ad_unit_id and day (in either order). This will provide optimal filtering.

2. If practical, all the ORDER BY can be added to that INDEX so that it can avoid having to sort the data. But there is an issue with ORDER BY bid DESC, budget DESC, campaign_id ASC. Note that there is a mixture of DESC and ASC. MySQL can only scan an index in one direction. This implies that all must be ASC or all must be DESC. (See note below) So, INDEX(ad_unit, day, bid, budget, campaign_id) is not as useful as you would like.

3. If the Optimizer could get past step 2, then adding any other columns mentioned anywhere in the SELECT (namely all of *), then the index would be come "covering". This gives a different variety of performance boost -- by doing all the work in the index, not having to touch the 'data'.

Note: Beginning with Version 8.0, MySQL will honor DESC in index declarations. So, you could say INDEX(ad_unit, day, bid DESC, budget DESC, campaign_id ASC), and the Optimizer could use it without sorting in item 2, above. (Pre-8.0, DESC was allowed, but ignored, in index declarations.)

Workaround? It would change the order of the output, but you might accept having all the columns ASC (or all DESC)?

Meanwhile, INDEX(ad_unit_id, day) is about all that is worth having.