Without an index, finding rows matching a WHERE condition requires scanning every row in the table (a "full table scan") — fine for a small table, ruinous for a large one. An index, most commonly a B-tree, maintains a separate sorted structure over one or more columns so the database can jump directly to matching rows. This speed comes at a real cost: every INSERT, UPDATE, or DELETE must also update every index on the affected columns, and each index consumes additional disk space.
Creating an index
Primary keys are indexed automatically by every major engine; other columns frequently used in WHERE, JOIN, or ORDER BY are good index candidates you add explicitly.
CREATE INDEX idx_users_email ON users(email);
-- now this is fast even on a huge table, instead of a full scan:
SELECT * FROM users WHERE email = 'alice@example.com';Composite indexes: column order matters
A composite index on (a, b) efficiently supports queries filtering on a alone, or on a AND b together — but NOT a query filtering on b alone, since the index is sorted by a first. This makes column order a real design decision, not an arbitrary choice.
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
-- uses the index efficiently:
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';
-- does NOT use this index efficiently (order_date alone, without customer_id):
SELECT * FROM orders WHERE order_date > '2026-01-01';When NOT to index
Every index slows down every write to that table, and adds storage overhead — indexing every column "just in case" is a genuine anti-pattern, not a safety measure. Indexes make the most sense on columns frequently filtered, joined, or sorted on, especially on tables with far more reads than writes; a small table, or a column rarely queried directly, often doesn't benefit enough to justify the write cost.