How Database Indexing Works: A Practical Guide

Posted by Kyle Hankinson May 20, 2022

---

An index makes queries faster by letting the database find rows without scanning the entire table. Understanding how they work helps you write faster queries and avoid common performance traps.

The Book Index Analogy

A database index works like a book's index. Without an index, finding a topic means reading every page (a full table scan). With an index, you look up the topic, get the page number, and go directly there.

A database index stores a sorted copy of specific columns along with pointers back to the full rows.

Creating an Index

CREATE INDEX idx_users_email ON users (email);

Now queries filtering by email can use the index instead of scanning every row:

-- Fast: uses the index
SELECT * FROM users WHERE email = 'alice@example.com';

-- Slow without an index: full table scan

Types of Indexes

B-Tree (Default)

The standard index type in every database. Supports equality (=), range (>, <, BETWEEN), and sorting (ORDER BY). This is what you get with a plain CREATE INDEX.

Hash

Fast for equality lookups only. Not useful for range queries or sorting. Available in PostgreSQL and MySQL (MEMORY engine).

GIN (PostgreSQL)

Generalized Inverted Index — designed for values that contain multiple elements: arrays, JSONB, and full-text search.

Full-Text

Purpose-built for searching text content. Available in MySQL, PostgreSQL, and SQL Server.

Composite Indexes

An index on multiple columns:

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

The leftmost prefix rule is critical: this index helps queries that filter by:

• customer_id alone
• customer_id AND order_date

But NOT:

• order_date alone (the index cannot be used)

Column order matters. Put the most selective (most-filtered) column first.

Clustered vs Non-Clustered

Clustered Index

The table data is physically stored in the index order. Each table can have only one.

• MySQL InnoDB: The primary key is always the clustered index.
• SQL Server: By default the primary key is clustered, but you can choose a different column.

Non-Clustered Index

A separate structure that points back to the table data. A table can have many non-clustered indexes.

When NOT to Index

Indexes are not free — they slow down INSERT, UPDATE, and DELETE because the index must be updated too.

Avoid indexing:

• Low-cardinality columns (e.g., a status column with only 3 possible values)
• Small tables — a full scan on 100 rows is faster than an index lookup
• Columns rarely used in WHERE, JOIN, or ORDER BY
• Tables with heavy write workloads where the insert overhead outweighs query gains

Verifying Index Usage with EXPLAIN

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';

-- SQL Server
SET SHOWPLAN_ALL ON;
SELECT * FROM users WHERE email = 'alice@example.com';
SET SHOWPLAN_ALL OFF;

Look for "index scan" or "index seek" in the output. If you see "table scan" or "seq scan," the index is not being used.

Common Mistakes

Over-indexing: Adding an index on every column wastes disk space and slows writes.

Indexing expressions without expression indexes: WHERE UPPER(email) = 'ALICE@EXAMPLE.COM' will not use an index on email. You need a functional index:

-- PostgreSQL
CREATE INDEX idx_users_email_upper ON users (UPPER(email));

-- MySQL 8.0+
CREATE INDEX idx_users_email_upper ON users ((UPPER(email)));

Forgetting covering indexes: If the index contains all columns the query needs, the database never has to look at the table at all (an index-only scan).

---