MySQL 8: Using Descending and Invisible Indexes the Right Way

1. Why ORDER BY DESC used to be expensive

InnoDB physically stores the entries of a B-tree index in ascending key order. When a query requested a descending sort, the optimizer before MySQL 8 could only walk that tree backward, from the last page to the first. For a single sort column that worked well enough, because a backward scan over an ascending index produces the same order as a forward scan over a hypothetical descending index. The optimizer only had to reverse the read direction, which barely added any internal overhead.

Things became critical once a query required several columns with different sort directions at once, for example status ascending and creation date descending at the same time. A single B-tree can, by definition, only represent one consistent physical order, so a backward scan was no longer enough to satisfy both requirements simultaneously. MySQL then had to fall back on an additional filesort, sorting the rows already retrieved through the index a second time explicitly, which noticeably costs time and memory on large result sets.

2. How a real descending index works internally

Since MySQL 8, an index definition can explicitly state ASC or DESC per column, and that is no longer pure syntax sugar, it actually changes how the keys are physically laid out in the B-tree. A column declared DESC is stored so that a simple forward scan already delivers the desired descending order. That eliminates both the backward scan and, in the decisive case of mixed directions, the follow up filesort entirely.

The difference shows up most clearly with composite indexes that mix directions. An index over two columns, where the first should sort ascending and the second descending, can be created as a genuine combined index, so both sort criteria are served by a single sequential scan. Before this feature existed, the only options were an expensive filesort or an artificial query rewrite that rarely worked cleanly in practice.

-- Classic index: both columns implicitly ascending
CREATE INDEX idx_status_created_asc
    ON sales_order (status, created_at);

-- Genuine combined index with mixed direction (MySQL 8+)
CREATE INDEX idx_status_created_mixed
    ON sales_order (status ASC, created_at DESC);

3. Practical example: sorting an order list by status and date

A common pattern in Magento adjacent reporting is an order list that should first group by status and, within each status, show the newest date first, for example to display open orders in descending chronological order on a support dashboard. Without a matching index, MySQL either falls back to an index scan followed by a filesort, or drops the index for sorting entirely and reads the full table.

With the mixed combined index shown above, the same query plan can cover the WHERE condition, the grouping by status and the descending date order in a single index pass. Especially on order tables with several million rows, which is far from unusual in a store that has grown over years, this difference separates a response time in the low millisecond range from a noticeable delay in the admin grid.

SELECT entity_id, increment_id, status, created_at
FROM sales_order
WHERE store_id = 1
ORDER BY status ASC, created_at DESC
LIMIT 25;

4. Making the effect visible with EXPLAIN ANALYZE

The most reliable way to prove the effect of a descending index is a direct before and after comparison with EXPLAIN ANALYZE. Without a matching combined index, the execution plan typically shows an extra sort stage for the intermediate results, visible as its own sort step in the plan, along with a measurably higher actual runtime compared to the raw row count.

With the new combined index, that extra sort stage disappears from the plan entirely, and rows are already read from the index in their final order. For recurring reports or heavily used admin grids, it is worth documenting this comparison as part of the query tuning process, so later schema changes do not accidentally reintroduce a filesort without anyone noticing.

EXPLAIN ANALYZE
SELECT entity_id, status, created_at
FROM sales_order
WHERE store_id = 1
ORDER BY status ASC, created_at DESC
LIMIT 25;

5. Invisible indexes: the basic idea and syntax

An invisible index remains fully maintained, meaning it is still updated on every INSERT, UPDATE and DELETE, but it no longer factors into the optimizer's decisions. As far as the execution plan is concerned, the table behaves as if that index did not exist, while it keeps consuming storage and write overhead unchanged in the background. That property makes it an ideal intermediate step before a final drop.

Switching between visible and invisible happens through a simple ALTER TABLE statement and takes effect immediately, without an expensive table copy or a lengthy rebuild. That sets it clearly apart from actually dropping or recreating an index, which on large InnoDB tables can easily mean several minutes or hours of lock time or online DDL work.

-- Make an index invisible to the optimizer
ALTER TABLE sales_order
    ALTER INDEX idx_legacy_customer_email INVISIBLE;

-- Revert if it turns out to still be needed
ALTER TABLE sales_order
    ALTER INDEX idx_legacy_customer_email VISIBLE;

6. A practical workflow for risk free index cleanup

In practice, a staged approach works well: first, information_schema.statistics combined with sys.schema_unused_indexes shows which indexes have not been read at all since the last database restart. Candidates from that list are not dropped right away, instead they are set to INVISIBLE for a defined period, for example two full business cycles including month end close and reporting.

During that period, monitoring, the slow query log and application metrics reveal whether any query measurably slows down or whether a previously unremarkable query plan suddenly switches to a filesort or a full table scan. If performance stays stable throughout the observation period, dropping the index becomes a low risk, well justified final step, rather than a gut decision based on a single snapshot.

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'magento2db';

7. Pitfalls: unique constraints and foreign keys

One important exception concerns unique indexes: even a UNIQUE index set to INVISIBLE keeps checking on every INSERT and UPDATE whether the value already exists, preventing duplicates exactly as before. It disappears as a possible access path for reads, but the integrity check itself stays fully active, a detail that is easy to miss if INVISIBLE is mistaken for fully disabled.

An index also cannot be made invisible if it is the only available structure backing an existing foreign key constraint. MySQL rejects that ALTER statement with an explicit error, because otherwise referential integrity checks would have to run without an efficient index path. In that case, an alternative, still visible index needs to be created first before the original one is allowed to disappear on a trial basis.

8. Combining both features into a safe migration strategy

When planning a switch from a classic, purely ascending index to a mixed descending index, both features combine well. First, the new mixed sort index is created alongside the old one, so both indexes exist at the same time and the optimizer can already pick the new one as soon as it beats the existing one in cost estimation.

Only once monitoring over a sufficient period confirms that the new index is consistently being used does the old index get set to INVISIBLE instead of being dropped immediately. This combination of parallel rollout and gradual invisibility reduces regression risk to nearly zero, since a single line ALTER statement is enough at any point to fully restore the previous state without rebuilding an index from scratch.

9. When the effort actually pays off

A genuine descending index pays off mainly when a query regularly and noticeably requires several columns with different sort directions at once, for example in admin grids, reporting views or paginated API endpoints over large result sets. For a plain single column sort, the explicit DESC declaration barely adds measurable value over the classic backward scan.

Invisible indexes, in turn, should become a fixed part of every index cleanup process, especially in Magento installations that have grown over years and accumulated many historically created, sometimes redundant indexes. The low cost of a trial invisibility switch is in no proportion to the risk of a premature, permanent drop, which in the worst case only surfaces days later during a rarely run monthly report.

10. Summary

11. FAQ: DESC and Invisible Indexes: Key Takeaways