Database Indexes

Overview

An index is a separate data structure that lets the database find matching rows without scanning the whole table. It works like the index at the back of a book: instead of reading every page, you jump straight to the right one. Indexes make reads on filtered or sorted columns much faster, but they add storage and slow down writes slightly.

Syntax / Usage

Create an index on the columns you frequently filter, join, or sort by using CREATE INDEX.

CREATE INDEX idx_users_email ON users (email);

-- Unique index also enforces no duplicates
CREATE UNIQUE INDEX idx_orders_number ON orders (order_number);

-- Composite index for queries that filter on both columns
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

Examples

A query filtering on an indexed column can use the index instead of a full scan:

SELECT * FROM users WHERE email = 'ada@example.com';

You can inspect whether an index is used with EXPLAIN:

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

Common Mistakes

• Adding indexes on columns that are never used in WHERE, JOIN, or ORDER BY
• Over-indexing a table, which slows down every INSERT, UPDATE, and DELETE
• Expecting an index to help when a query wraps the column in a function, which usually disables it
• Putting composite index columns in the wrong order for how you actually query
• Forgetting that primary keys and unique constraints already create indexes

See Also

databases-keys-and-relationships databases-relational-basics databases-transactions