Introduction
Knowing that indexes speed up reads is only half the picture — effective indexing requires strategy: which columns to combine into a composite index, in what order, whether to make an index 'covering' so it can satisfy a query without touching the table at all, and, just as importantly, recognizing when adding an index is the wrong call. Over-indexing a write-heavy table or indexing a low-cardinality column can hurt more than it helps.
Cricket analogy: Knowing an index helps a scorer find data fast is only half of it; deciding whether to compound (batter, venue) or (venue, batter), and skipping an index on 'toss_result' since it only has two values, is the real strategic skill.
Syntax
-- Composite index: column order matters
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
-- Covering index: includes all columns the query needs, so the table itself
-- never has to be touched (an 'index-only scan')
CREATE INDEX idx_orders_covering ON orders (customer_id, status, order_id, created_at);
EXPLAIN SELECT order_id, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'shipped';Explanation
In a composite (multi-column) index, the column order determines which query patterns it can serve efficiently: the index is only useful for filtering on a leading prefix of its columns, similar to how a phone book sorted by (last_name, first_name) helps you find 'Smith' but is useless for finding everyone named 'John' regardless of surname. As a rule of thumb, put the column used in equality filters before columns used in range filters, and place the most selective or most frequently filtered column first when queries vary. A covering index goes further by including every column a query needs — either as key columns or, in engines that support it, as non-key 'included' columns — so the engine can answer the query directly from the index (an index-only scan) without a separate lookup into the table's heap. Not every column deserves an index, however. Indexing a low-cardinality column (like a boolean or a status flag with only three values) rarely helps, because the optimizer often still needs to read a large fraction of matching rows, making a sequential scan just as fast or faster. Small tables that fit in a few pages gain little from an index, since a full scan is already cheap. And on write-heavy tables, each additional index adds real overhead to every INSERT/UPDATE/DELETE, so indexes there should be reserved for columns with clearly justified, frequent query needs.
Cricket analogy: A composite index on (venue, batter) is like a scorebook sorted by ground first, then player: it finds every game at Eden Gardens fast but is useless for finding all of Kohli's innings across venues; indexing 'toss_result' rarely helps since it's only heads or tails, and small club-level tables barely benefit from any index at all.
Example
-- Good: customer_id (equality, high cardinality) first, status (equality) second
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);
-- Serves: WHERE customer_id = ? (uses prefix)
-- Serves: WHERE customer_id = ? AND status = ? (uses full index)
-- Does NOT efficiently serve: WHERE status = ? (status is not a leading column)
-- Poor choice: indexing a boolean flag alone on a large table
CREATE INDEX idx_orders_is_paid ON orders (is_paid); -- low cardinality, weak benefit
-- Poor choice: adding this index to a table with heavy insert volume
-- and no query that actually filters on is_paid aloneAnalysis
The idx_orders_customer_status index efficiently serves any query filtering on customer_id alone, or on customer_id and status together, because both are usable as a matching prefix of the index. A query that filters only on status cannot use this index efficiently, since status is not the leading column — the engine would have to scan the whole index. The idx_orders_is_paid index illustrates the low-cardinality trap: if roughly half the rows are paid and half are not, an index scan would still need to fetch a huge fraction of the table, so the optimizer will likely ignore the index and choose a sequential scan anyway, leaving the index as pure write overhead. The right response in that case is usually not to add the index at all, or to make it useful only as part of a more selective composite index.
Cricket analogy: The idx_matches_team_result index efficiently serves queries filtering on team alone or team and result together since both form a matching prefix, but a query filtering only on result cannot use it efficiently; the idx_matches_is_day_night index shows the low-cardinality trap since day/night is roughly a coin flip, so the optimizer likely ignores it and scans instead.
Key Takeaways
- In a composite index, column order determines which queries can use it — only a leading prefix of columns is efficiently searchable.
- Put equality-filtered, high-selectivity columns before range-filtered columns in composite index order.
- A covering index includes every column a query needs, enabling an index-only scan that avoids touching the table.
- Avoid indexing low-cardinality columns (few distinct values), since the optimizer often can't use them efficiently anyway.
- Small tables rarely benefit from indexes since a full scan is already cheap.
- On write-heavy tables, weigh each index's read benefit against its cost to every INSERT/UPDATE/DELETE.
Practice what you learned
1. In a composite index on (customer_id, status), which query can it serve efficiently?
2. What is a covering index?
3. Why is indexing a low-cardinality boolean column often ineffective?
4. Which scenario is the LEAST likely to benefit from adding a new index?
Was this page helpful?
You May Also Like
Indexes and How They Work
How database indexes use B-tree structures to speed up lookups, and why they add overhead to writes.
Query Optimization Basics
Foundational techniques for writing queries the optimizer can execute efficiently.
Execution Plans
How to read EXPLAIN output to understand and diagnose how the database will run a query.