How I Chose Composite Indexes on a 500K-Record System

Indexes are the first thing people suggest when a database query is slow. Add an index, problem solved. The reality is that adding the wrong index does not help, and adding indexes on every column creates write overhead without solving the actual performance problem.

The indexes on this system came from reading the query patterns first, not from guessing.

Problem

Before adding any indexes, the slowest queries all had the same pattern in EXPLAIN: Full table scan on tables with 500K+ rows. MySQL was reading every row to find the ones matching the WHERE clause.

The transactions table had no index at all. Queries filtered by store_id and transaction_date were scanning the full table on every request. With 8 brands running reports simultaneously, that is 8 concurrent full scans of the same table.

Individual column indexes would have helped with single-column filters. But the actual query patterns always filtered on two or three columns together. A single index on store_id helps queries that only filter by store_id. It does not help queries that filter by store_id AND transaction_date, because MySQL still has to scan all rows for that store to apply the date filter.

Solution

Composite indexes ordered to match the actual query patterns.

For transactions: INDEX (store_id, transaction_date). Queries filtering by store_id land on the right section of the index. MySQL then applies the date filter within that section. Much faster than filtering the full table.

For inventory: INDEX (store_id, stock_level, is_active). The report queries filter by all three columns. The composite index covers the full filter in one index access.

For stores: INDEX (is_active, region, store_id). Reports often filter active stores within a region. The index is ordered to match: filter active first, then region, then look up the store_id.

The column order in a composite index matters. Leading columns filter the most rows. Trailing columns narrow the result within that filtered set. Putting the highest-selectivity filter first usually performs best.

Result

EXPLAIN changed from Full table scan to Using index for the main report queries.

Heavy reports dropped from 2 minutes to 30 to 40 seconds.

The specific indexes added are matched to the actual WHERE and JOIN patterns in the codebase. Generic index advice will not get you here. You have to look at what your queries actually do and add indexes that match.