Understanding Database Indexes

What is an Index?

An index is a data structure that speeds up data retrieval — like a book's index lets you find a topic without reading every page. Without indexes, the database scans every row.

Creating an Index

CREATE INDEX idx_email ON users(email);

-- Now this is fast even with millions of rows:
SELECT * FROM users WHERE email = 'sara@x.com';

How It Works

Most indexes use a B-tree, turning a slow full-table scan (O(n)) into a fast lookup (O(log n)).

When to Index

• Columns in WHERE, JOIN, and ORDER BY.
• Foreign keys.
• Frequently searched columns.

The Cost of Indexes

• Slower INSERT/UPDATE (index must update too).
• Extra disk space.
• Don't index everything — only what you query.

FAQs

Why is my query still slow with an index?

Use EXPLAIN to check if the index is actually used. More in our Database section.

What is a composite index?

An index on multiple columns, useful for multi-column queries.