Indexing Basics

What it does

Without an index the database does a full table scan: it reads every row to find matches. With an index on the searched column it jumps almost directly to the matching rows. Most relational databases use a tree structure (a B-tree) for general indexes.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Primary keys and unique constraints normally get an index automatically. Foreign key columns often do not, and are good candidates.

What to index

• Columns used often in WHERE, JOIN ... ON and ORDER BY.
• Columns with many different values. An index on a yes/no column rarely helps.
• A composite index covers several columns, and column order matters: an index on (last_name, first_name) helps a search by last name but usually not by first name alone.

The costs

• Every INSERT, UPDATE and DELETE must also maintain each index, so writes get slower.
• Indexes use disk and memory.
• Wrapping a column in a function (WHERE LOWER(email) = ...) or searching with a leading wildcard (LIKE '%abc') often prevents the index from being used.

So more indexes are not always better.

Reading the query plan

Databases can show their plan for a query: EXPLAIN in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite, or the graphical execution plan in SQL Server tools. Look for scans on large tables. Measure before and after, and test with realistic data volumes, since small development data hides the problem. For the wider picture see Performance Basics.

Query design matters too

Fetch only the columns and rows you need, and avoid running one query per item in a loop (the “N+1” problem). See SQL Fundamentals.

Try it

Create a table with 100,000 generated rows, run EXPLAIN on a lookup by a non-indexed column, then create an index and compare the plan and the timing.