Indexing and Query Performance: EXPLAIN, Composite Indexes

Indexing and Query Performance

Every slow query in production traces back to one of a handful of root causes, and most of them are index-related: a missing index, a wrong-order composite index, an index that can't be used because of how the query was written, or a query that reads far more rows than it needs to. This guide covers how indexes actually work under the hood, when they help and when they don't, and how to read an EXPLAIN plan well enough to fix a slow query with confidence instead of guesswork.

1. How an Index Actually Works

A B-tree index is a sorted, navigable structure that lets the database find rows without scanning the whole table — the same reason a phone book's alphabetical ordering beats reading every entry.

A B-tree index turns a lookup from O(n) (scan every row) into roughly O(log n) (a few page reads to navigate from root to leaf) — on a table with a million rows, that's the difference between reading a million pages and reading three or four.

Clustered vs secondary indexes (InnoDB)

InnoDB's primary key is the table — rows are physically stored in primary-key order in what's called the clustered index. Every other index is a secondary index that stores the indexed column(s) plus the primary key, and requires an extra lookup back into the clustered index to fetch the full row.

2. When Indexes Help — and When They Don't

-- Index used: equality on an indexed column
CREATE INDEX idx_orders_status ON orders (status);
SELECT * FROM orders WHERE status = 'PENDING';   -- index seek
 
-- Index NOT used: function applied to the indexed column
SELECT * FROM orders WHERE YEAR(created_at) = 2026;  -- full scan, function
                                                        -- prevents index use
 
-- Fix: rewrite to a sargable range predicate on the raw column
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';  -- index usable

-- Non-sargable: leading wildcard prevents index use
SELECT * FROM products WHERE name LIKE '%widget%';
 
-- Sargable: prefix match CAN use a B-tree index
SELECT * FROM products WHERE name LIKE 'widget%';
 
-- For genuine substring search, use a full-text or search-engine index instead
-- ALTER TABLE products ADD FULLTEXT INDEX idx_name_fts (name);
-- SELECT * FROM products WHERE MATCH(name) AGAINST('widget' IN NATURAL LANGUAGE MODE);

Cases where an index won't be used (or shouldn't be added)

3. Composite Indexes and Column Order

A composite (multi-column) index is sorted by its first column, then by its second column within each value of the first, and so on — like sorting a phone book by last name, then first name.

CREATE INDEX idx_orders_customer_status_date
ON orders (customer_id, status, created_at);

The leftmost-prefix rule

This composite index can serve queries that filter on a left-to-right prefix of its columns:

-- Uses the full index: customer_id, status, created_at all match the prefix
SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING' AND created_at > '2026-08-01';
 
-- Uses the index (prefix: customer_id, status)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'PENDING';
 
-- Uses the index (prefix: customer_id only)
SELECT * FROM orders WHERE customer_id = 42;
 
-- CANNOT use this index efficiently — status is not a leftmost prefix
SELECT * FROM orders WHERE status = 'PENDING';
 
-- CANNOT use this index efficiently — created_at alone skips customer_id and status
SELECT * FROM orders WHERE created_at > '2026-08-01';

Order-sensitive design: high-cardinality-first isn't always right

A common myth is "always put the highest-cardinality column first." In practice, column order should match how your queries actually filter — equality columns before range columns, and columns present in the most common/most expensive queries first. A single well-designed composite index often replaces three or four narrower single-column indexes.

4. Covering Indexes

A covering index contains every column a query needs — the database can answer the query entirely from the index without a lookup back into the clustered index (a "table access" or "key lookup").

-- Query needs only these three columns
SELECT customer_id, status, created_at
FROM orders
WHERE customer_id = 42 AND status = 'PENDING';
 
-- This index COVERS the query — no need to touch the base table at all
CREATE INDEX idx_orders_covering
ON orders (customer_id, status, created_at);

-- Add extra columns purely to make an index covering, via INCLUDE-style pattern
-- MySQL: append trailing columns to the composite index itself
CREATE INDEX idx_orders_covering_wide
ON orders (customer_id, status, created_at, total_cents);
-- Now `SELECT total_cents` alongside the WHERE columns is also covered
 
-- PostgreSQL: explicit INCLUDE clause for non-key covering columns
-- CREATE INDEX idx_orders_covering_wide
-- ON orders (customer_id, status)
-- INCLUDE (created_at, total_cents);

5. Reading EXPLAIN Plans

EXPLAIN shows you the execution plan the query optimizer chose — the tool for turning "this query is slow" into "here is specifically why."

EXPLAIN
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PENDING' AND o.created_at > '2026-08-01'
ORDER BY o.created_at DESC
LIMIT 20;

+----+-------------+-------+--------+----------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type   | possible_keys        | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+--------+----------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | o     | range  | idx_status_created    | idx_st..| 5       | NULL  | 1200 |   100.00 | Using where |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY               | PRIMARY | 8       | o.cid |    1 |   100.00 | NULL        |
+----+-------------+-------+--------+----------------------+---------+---------+-------+------+----------+-------------+

Key columns to read

-- EXPLAIN ANALYZE: actually runs the query and reports real timing per step,
-- not just the optimizer's estimate — the most reliable diagnostic tool
EXPLAIN ANALYZE
SELECT o.id, o.status, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC
LIMIT 20;

6. Common Performance Bottlenecks

N+1 queries

// BAD: 1 query for orders, then N queries for each order's customer
List<Order> orders = orderRepository.findByStatus("PENDING");
for (Order order : orders) {
    Customer c = customerRepository.findById(order.getCustomerId());  // N queries!
    // ...
}

-- GOOD: a single join fetches everything needed in one round trip
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PENDING';

Filesort and temporary tables

-- ORDER BY on an unindexed column forces a filesort (in-memory or on-disk sort)
SELECT * FROM orders ORDER BY total_cents DESC LIMIT 20;  -- Using filesort
 
-- Add an index matching the ORDER BY to avoid sorting at all
CREATE INDEX idx_orders_total ON orders (total_cents DESC);

Over-fetching

-- BAD: SELECT * pulls every column, including large TEXT/BLOB fields never used
SELECT * FROM products WHERE category_id = 12;
 
-- GOOD: select only what's needed — smaller network payload, more likely covered by an index
SELECT id, name, price_cents FROM products WHERE category_id = 12;

Index bottleneck decision tree

7. Index Maintenance Overhead

# Check index usage in MySQL — flags indexes never touched since last stats reset
# (query against performance_schema)

-- MySQL: find unused indexes via performance_schema
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'ecommerce'
ORDER BY object_schema, object_name;

Key takeaways

• InnoDB's primary key is the clustered index — the table's physical row order. Every secondary index carries the primary key, so a bulky/random primary key bloats every index on the table.
• A predicate must be sargable to use an index — wrapping the indexed column in a function or a leading wildcard forces a full scan.
• Composite index column order matters: equality columns first, range columns last, ordered to match your actual queries — not by cardinality alone.
• A covering index lets the database answer a query from the index alone, skipping the lookup into the clustered index entirely — look for Using index in EXPLAIN.
• EXPLAIN's type column tells you the access strategy; ALL on a large table is the first thing to investigate, but use EXPLAIN ANALYZE for real timing, not just estimates.
• N+1 queries are invisible in small local datasets and become a real production bottleneck only at scale — watch for them in code review, not just incident response.
• Every index has a write-time cost — audit and drop indexes that usage stats show are never read.
• Using filesort and Using temporary in EXPLAIN's Extra column are signals to add an index matching your ORDER BY/GROUP BY, not just your WHERE clause.

Interview Questions

• How does a B-tree index reduce a table scan's O(n) lookup to roughly O(log n)?
• What is the difference between a clustered index and a secondary index in InnoDB?
• Why does a large or random primary key (like a random UUID) hurt the performance of every secondary index on the table, not just the primary key lookup?
• What makes a predicate "non-sargable"? Give an example and show how to rewrite it to use an index.
• Explain the leftmost-prefix rule for composite indexes with an example query that can and cannot use a given index.
• How should you order columns in a composite index when you have both equality and range predicates?
• What is a covering index, and how do you recognize one in an EXPLAIN plan?
• Walk through the key columns of a MySQL EXPLAIN output: type, key, rows, filtered, Extra.
• What's the difference between EXPLAIN and EXPLAIN ANALYZE?
• What is the N+1 query problem, and why is it often invisible until production scale?
• When would a full table scan (type: ALL) actually be the optimizer's correct choice?
• What does Using filesort in the Extra column mean, and how would you eliminate it?
• Why does every index added to a table have an ongoing cost, even if it's never used for reads?
• How would you find and remove indexes that are never used in a production database?