SQL Indexes

An index is a separate, sorted data structure that a database engine builds on top of a table so it can find rows without scanning every one of them. Think of it like the index at the back of a textbook: instead of reading every page to find “transactions,” you look it up alphabetically and jump straight to the right page. Indexes are the single biggest lever you have for query performance, but they aren’t free — they cost disk space and slow down writes, so knowing when and how to use them is essential.

Overview: How Indexes Work

Without an index, the database engine answers a filtered query with a full table scan: it reads every row, checks whether it matches your WHERE clause, and keeps the ones that do. That’s O(n) work — fine for a hundred rows, painful for a hundred million.

Most relational databases (SQLite, MySQL’s InnoDB, PostgreSQL, SQL Server) implement indexes using a B-tree (balanced tree) structure. A B-tree keeps indexed values in sorted order across a tree of pages, so the engine can find any value in O(log n) comparisons — a handful of page reads even for a huge table. Each entry in the index stores the indexed column’s value plus a pointer back to the actual table row (in SQLite this pointer is the internal rowid; in InnoDB it’s the primary key).

When you run a query, the database’s query planner (also called the query optimizer) decides how to execute it. It looks at the available indexes, estimates how selective each one is (how many rows it would eliminate), and picks a plan: an index seek (jump straight to matching rows via the B-tree) or a full table scan. A well-designed index turns a query that reads a million rows into one that reads a few dozen.

Indexes aren’t only about single-column lookups. A composite (multi-column) index stores rows sorted first by its first column, then by its second column within each value of the first, and so on — much like a phone book sorted by last name, then first name. This ordering matters enormously, as you’ll see in the examples below.

Syntax

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name
ON table_name (column1, ...);

DROP INDEX index_name;
Clause Meaning
CREATE INDEX Builds a new, ordinary (non-unique) index on one or more columns.
CREATE UNIQUE INDEX Builds an index that also enforces that no two rows share the same combination of indexed values.
index_name A name you choose for the index; must be unique within the database (or schema, in some engines).
table_name The table being indexed.
(column1, column2, ...) One or more columns, in the order that matters for composite indexes (leftmost column first).
DROP INDEX Removes an index. In SQLite and PostgreSQL you write DROP INDEX index_name; MySQL requires DROP INDEX index_name ON table_name.

Note that a PRIMARY KEY and, in most engines, a column marked UNIQUE already get an index automatically — you never need to create a separate index for those.

Examples

Example 1: A basic single-column index

CREATE TABLE products_catalog (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL
);

INSERT INTO products_catalog (product_id, product_name, category, price) VALUES
(1, 'Wireless Mouse', 'Electronics', 25.99),
(2, 'Mechanical Keyboard', 'Electronics', 89.99),
(3, 'Standing Desk', 'Furniture', 349.00),
(4, 'Office Chair', 'Furniture', 219.50),
(5, 'USB-C Hub', 'Electronics', 34.75);

CREATE INDEX idx_products_catalog_category
ON products_catalog(category);

SELECT product_name, price
FROM products_catalog
WHERE category = 'Electronics'
ORDER BY price;

Result:

product_name price
Wireless Mouse 25.99
USB-C Hub 34.75
Mechanical Keyboard 89.99

The index on category lets the engine jump straight to the block of rows where category = 'Electronics' instead of checking all five rows. This is exactly the kind of column worth indexing: it’s used often in WHERE clauses and has moderate selectivity (multiple distinct values).

Example 2: A composite index for a two-column filter

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  sale_date TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO sales (sale_id, region, sale_date, amount) VALUES
(1, 'West', '2026-01-05', 120.00),
(2, 'East', '2026-01-06', 75.50),
(3, 'West', '2026-02-10', 200.00),
(4, 'West', '2026-01-20', 60.00),
(5, 'East', '2026-03-01', 300.00);

CREATE INDEX idx_sales_region_date
ON sales(region, sale_date);

SELECT sale_id, sale_date, amount
FROM sales
WHERE region = 'West' AND sale_date >= '2026-01-10'
ORDER BY sale_date;

Result:

sale_id sale_date amount
4 2026-01-20 60.00
3 2026-02-10 200.00

The composite index (region, sale_date) is sorted first by region, then by date within each region. Because the query filters on region (the leading column) with equality and then on sale_date (the second column) with a range, the engine can seek directly to the West block and scan only its date-ordered entries from 2026-01-10 onward — it never has to look at any East row.

Example 3: A unique index for data integrity and lookups

CREATE TABLE newsletter_subscribers (
  subscriber_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_subscribers_email
ON newsletter_subscribers(email);

INSERT INTO newsletter_subscribers (subscriber_id, email) VALUES
(1, 'ana@example.com'),
(2, 'ben@example.com'),
(3, 'cleo@example.com');

SELECT subscriber_id, email
FROM newsletter_subscribers
ORDER BY email;

Result:

subscriber_id email
1 ana@example.com
2 ben@example.com
3 cleo@example.com

A UNIQUE INDEX does two jobs at once: it speeds up lookups by email the same way an ordinary index would, and it rejects any INSERT or UPDATE that would create a duplicate email — the database checks the index on every write, which is also why unique indexes add a little more write overhead than ordinary ones.

How It Works Step by Step (Under the Hood)

For a filtered query, the query planner evaluates conditions logically in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Indexes primarily accelerate the WHERE stage (and, when the index’s column order matches, the ORDER BY stage too, letting the engine skip a separate sort). You can see the planner’s actual choice with EXPLAIN QUERY PLAN:

CREATE TABLE big_table (
  id INTEGER PRIMARY KEY,
  status TEXT,
  created_at TEXT
);

INSERT INTO big_table (id, status, created_at) VALUES
(1, 'active', '2026-01-01'),
(2, 'inactive', '2026-01-02'),
(3, 'active', '2026-01-03'),
(4, 'active', '2026-01-04');

CREATE INDEX idx_big_table_status
ON big_table(status);

EXPLAIN QUERY PLAN
SELECT * FROM big_table WHERE status = 'active';

Result (plan output): a single row whose detail column reads something like SEARCH big_table USING INDEX idx_big_table_status (status=?). That word SEARCH tells you the planner used the index to seek directly to matching rows. If you dropped the index and re-ran the same query, the plan would instead say SCAN big_table, meaning every row is read and tested — the exact difference an index is built to eliminate. (The precise wording varies slightly by database engine, but the seek-vs-scan distinction is universal.)

Common Mistakes

Mistake 1: Filtering on a composite index’s non-leading column

A composite index only helps a query that filters on its leftmost columns (as a prefix). Skip the leading column and the index becomes far less useful — the engine usually falls back to a full scan.

CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  last_name TEXT NOT NULL,
  first_name TEXT NOT NULL
);

INSERT INTO contacts (contact_id, last_name, first_name) VALUES
(1, 'Smith', 'Ana'),
(2, 'Smith', 'Ben'),
(3, 'Jones', 'Cleo'),
(4, 'Diaz', 'Elle');

CREATE INDEX idx_contacts_lastname_firstname
ON contacts(last_name, first_name);

-- Runs fine, but the index can't be used for a seek:
SELECT * FROM contacts WHERE first_name = 'Ben';

This query still returns the correct row (2, Smith, Ben) — it isn’t an error, just inefficient, because first_name is the second column of the index, not the first. The fix is to include the leading column in the filter, or to build a separate index specifically on first_name if that lookup pattern is common:

SELECT * FROM contacts
WHERE last_name = 'Smith' AND first_name = 'Ben';

This corrected query returns the same single row, but now the planner can seek using last_name first and narrow to first_name within that block — a true index seek instead of a scan.

Mistake 2: Assuming a unique index just “limits” duplicates gracefully

A unique index doesn’t silently drop duplicate writes — it makes them fail outright:

CREATE TABLE newsletter_subscribers2 (
  subscriber_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_subscribers2_email
ON newsletter_subscribers2(email);

INSERT INTO newsletter_subscribers2 (subscriber_id, email) VALUES (1, 'ana@example.com');
INSERT INTO newsletter_subscribers2 (subscriber_id, email) VALUES (2, 'ana@example.com');
-- Error: UNIQUE constraint failed: newsletter_subscribers2.email

The second INSERT raises an error and the row is rejected, because ana@example.com already exists in the index. Applications must catch this error (or check for existence first with a SELECT) rather than assuming the insert will just quietly succeed or overwrite.

Best Practices

  • Index columns that appear often in WHERE, JOIN ON, and ORDER BY clauses — indexing a column you never filter or sort on wastes space and slows writes for no benefit.
  • Favor columns with high selectivity (many distinct values). Indexing a boolean or a column with only two or three possible values rarely helps, since the planner may decide a scan is just as fast.
  • For queries that filter on multiple columns together, build one composite index with the most selective or most frequently equality-filtered column first, rather than several single-column indexes.
  • Don’t over-index: every extra index slows down every INSERT, UPDATE, and DELETE, because the engine has to update each index in addition to the table itself.
  • Remember that primary keys and columns declared UNIQUE are already indexed automatically — don’t create a redundant index on them.
  • Periodically check actual usage with EXPLAIN QUERY PLAN (SQLite/PostgreSQL) or EXPLAIN (MySQL) and drop indexes that the planner never chooses.
  • Use DROP INDEX to remove an index cleanly when it’s no longer needed:
CREATE INDEX idx_products_price ON products(price);
DROP INDEX idx_products_price;

Practice Exercises

  • Exercise 1: Given an orders table with columns order_id, customer_id, order_date, and amount, write a CREATE INDEX statement that would best speed up a query filtering by customer_id and then sorting the matching rows by order_date.
  • Exercise 2: You have a users table with a username column that must never contain duplicates. Write the statement that both enforces this rule and speeds up lookups by username.
  • Exercise 3: A colleague created an index on a gender column (with only two possible values) in a 10-million-row table, hoping it would speed up a report. Explain, in your own words, why this index is unlikely to help — and what EXPLAIN QUERY PLAN would likely show if you ran the filtering query.

Summary

  • An index is a sorted B-tree structure that lets the database engine seek directly to matching rows instead of scanning the whole table.
  • CREATE INDEX name ON table(column, ...) builds an ordinary index; CREATE UNIQUE INDEX also forbids duplicate values.
  • Composite indexes are sorted by their first column, then their second, and so on — they only accelerate queries that filter on a leftmost prefix of those columns.
  • EXPLAIN QUERY PLAN reveals whether a query actually uses an index (SEARCH ... USING INDEX) or falls back to a full scan (SCAN).
  • Indexes speed up reads but slow down writes and use extra storage, so index deliberately — high-selectivity, frequently-filtered columns first.
  • Primary keys and UNIQUE columns are already indexed automatically; avoid redundant indexes on them.