SQL Indexing Strategies for Faster Queries

A missing index is the single most common cause of a slow query on an otherwise well-designed database. But indexes aren't free — they speed up reads and slow down writes, and a poorly ordered composite index can be almost useless. This guide covers how B-tree indexes work, how to design composite indexes correctly, and how to verify your queries are actually using them with EXPLAIN.

How a B-Tree Index Works

Most database indexes — including the default index type in MySQL's InnoDB and PostgreSQL — are B-tree (balanced tree) structures. Instead of scanning every row to find a match, the database walks down the tree comparing values, similar to how you'd find a word in a paper dictionary by jumping to a section rather than reading page by page. This turns an O(n) full table scan into an O(log n) lookup.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- This query can now use the index to jump straight to matching rows
SELECT * FROM orders WHERE customer_id = 4521;

B-tree indexes are efficient for equality (=), range (>, <, BETWEEN), and sorting (ORDER BY) on the indexed column, because a B-tree keeps values in sorted order.

Composite Indexes — Why Column Order Matters

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 — the same way a phone book is sorted by last name, then first name. This means the column order you choose determines which queries the index can actually help:

CREATE INDEX idx_orders_status_date ON orders (status, order_date);

-- Uses the index efficiently (leftmost column present)
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE status = 'pending' AND order_date >= '2026-01-01';

-- Cannot use this index efficiently (skips the leftmost column)
SELECT * FROM orders WHERE order_date >= '2026-01-01';

This is called the "leftmost prefix" rule. As a general guideline: put the column used in equality filters (=) first, and the column used for range filters or sorting last. If you frequently query by order_date alone, you'll need a separate index for that.

When Indexes Hurt — The Write Cost

Every index must be updated whenever a row is inserted, updated, or deleted, so more indexes mean more work per write:

• A table with 10 indexes can be noticeably slower to INSERT into than one with 2
• Indexes also consume disk space — sometimes more than the table data itself for wide composite indexes
• An index on a low-cardinality column (like a boolean is_active flag) rarely helps, since it can't narrow the search much
• Unused indexes should be dropped — they cost write performance and storage with no read benefit

The rule of thumb: index columns that appear often in WHERE, JOIN ... ON, and ORDER BY clauses on tables that are read far more often than they're written. Avoid indexing every column "just in case."

Reading a Query Plan with EXPLAIN

EXPLAIN shows you how the database intends to execute a query — whether it uses an index or scans the whole table:

-- MySQL
EXPLAIN SELECT * FROM orders WHERE customer_id = 4521;
-- Look at the "type" column: "ref" or "range" is good, "ALL" means full table scan

-- PostgreSQL (use ANALYZE to see actual execution time, not just the plan)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4521;
-- Look for "Index Scan" or "Index Only Scan" vs "Seq Scan"

A Seq Scan (PostgreSQL) or type: ALL (MySQL) on a large table is a strong signal that either an index is missing, the existing index doesn't match the query's filter columns, or the optimizer decided a full scan was cheaper — which can happen when the query returns a large fraction of the table anyway.

Common Indexing Mistakes

1. Wrapping an indexed column in a function in WHERE (e.g. WHERE YEAR(order_date) = 2026) prevents the index from being used — filter with a range instead: WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'
2. Creating a composite index in the wrong column order for your actual query patterns
3. Forgetting to index foreign key columns used in JOINs
4. Indexing a column with very low cardinality, like a status flag with only 2-3 possible values
5. Never checking EXPLAIN after adding an index to confirm it's actually being used

Frequently Asked Questions