Senior LevelSQL & MySQL
How do Descending Indexes optimize multi-column sorting with mixed ASC/DESC order?
❓ Interview Prompt: Explain Descending Indexes in MySQL 8.0, Forward vs Backward index scans, and optimizing queries with mixed sorting (`ORDER BY A ASC, B DESC`).
In MySQL 5.7 and earlier, indexes were always scanned in ascending order. Scanning an index backwards ('Backward Index Scan') caused severe performance penalties!
MySQL 8.0 Descending Indexes:
MySQL 8.0 supports true Descending Indexes ('CREATE INDEX idx ON table (a ASC, b DESC)'):
•
Stores index nodes in mixed directional order.
•
Completely eliminates 'Using filesort' overhead for complex multi-column queries sorting on mixed directions ('ORDER BY category ASC, price DESC').
Code Implementation Example
sql
-- Creating a Descending Index for mixed directional sorting
CREATE INDEX idx_products_cat_price ON products(category_id ASC, price DESC);
-- Query uses index directly without FileSort!
EXPLAIN SELECT id, category_id, price
FROM products
ORDER BY category_id ASC, price DESC;💡Optimizing mixed directional sorting with a Descending Index.
🎯 Interview Key Takeaway
Create Descending Indexes in MySQL 8 to eliminate filesort overhead for queries with mixed ASC/DESC sorting.
#MySQL 8#Descending Index#Indexing#FileSort#Senior
Back to all SQL & MySQL Questions→