MySQL query optimization without the textbook: lessons from a 40M-row operational database

Standard relational textbooks teach you to index foreign keys and run EXPLAIN. However, inside a 40-million-row production database running under a continuous write load, basic indexing strategies break down. The query optimizer can make catastrophic decisions based on stale statistics, selecting scanning paths that lock entire tables.

The Operational Scenario

Our target was a high-frequency ledger table storing transactional entries. A reporting query designed to fetch recent, unprocessed status updates for specific client groups began taking upwards of 12 seconds to return data, bringing the application server to its knees.

SELECT * FROM ledger_entries 
WHERE status = 'unprocessed' 
  AND client_group_id = 9422 
ORDER BY created_at DESC 
LIMIT 50;

Why EXPLAIN is Incomplete

Running EXPLAIN on the original query showed that MySQL elected to use a single-column index on status. Since 'unprocessed' represented 90% of the active table records, the index cardinality was incredibly low. The engine ended up scanning millions of records to filter the client group and sort the result set.

Type: ref | Key: idx_status | Rows: 4,210,094 | Extra: Using where; Using filesort

The execution engine fell back to a filesort, writing intermediate chunks to disk because the sorting operation could not be resolved directly by reading an ordered index structure.

Building Compound Indexes

To resolve multi-column filters and sorting without filesort, we must construct a composite (compound) index. The order of columns in a composite index is critical:

• Place exact equality columns first (client_group_id = 9422).
• Place high-cardinality equality fields second (status = 'unprocessed').
• Place the sorting column last (created_at) to allow the engine to read pre-ordered entries.

-- Create the optimized composite index
ALTER TABLE ledger_entries 
ADD INDEX idx_client_status_created (client_group_id, status, created_at DESC);

Refactoring the Query

After creating the composite index, we refactored the query to select only primary keys first in a subquery (a covering index pattern). This prevents MySQL from pulling wide rows (all columns) into temporary buffer tables during sorting.

SELECT l.* 
FROM ledger_entries l
JOIN (
    SELECT id 
    FROM ledger_entries 
    WHERE client_group_id = 9422 
      AND status = 'unprocessed' 
    ORDER BY created_at DESC 
    LIMIT 50
) AS sub ON l.id = sub.id;

The optimized structure drops the execution time from 12.4 seconds to 3.2 milliseconds. The EXPLAIN output shifts from a heavy 'Using filesort' scan to a fast index look-up with zero disk-based sort activity.