Skip to content

Index

In one line

An index is a data structure that makes finding rows by a column much faster, like a book's index for quick lookup.

In simple words

An index is a separate data structure that the database maintains to find rows quickly. Without an index, the database must scan every row in a table (a "full table scan") to find matches. With an index, it can jump directly to the relevant rows — like using a book's index instead of flipping through every page.

Indexes are created on columns that are frequently searched, joined, or used in WHERE clauses. The most common type is a B-tree index, which keeps values sorted for fast lookups and range queries.

The trade-off is that indexes take up extra storage and slow down writes (INSERT, UPDATE, DELETE) because the index must be updated too. You should index columns you query often, but not every column.

Example

sql
-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- This query is now fast:
SELECT * FROM users WHERE email = '[email protected]';

Without the index, the database scans every row. With the index, it finds the matching row instantly.

Common confusions

  • Confused with: primary key

    The difference: A primary key is a uniqueness constraint; an index is a performance structure. Primary keys automatically get an index, but you create other indexes manually for performance.

Related terms

Related Resources

Related Lessons

  • Indexes

    Learn how indexes speed up queries.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary