Skip to content
Tutorials Center
My learning

Topics

Lesson 75 of 125 ยท Designing Databases

Indexes

Learn how SQL indexes make queries fast. Create single, unique and composite indexes, read EXPLAIN QUERY PLAN output and know when an index helps or hurts.

Ask a database for the orders of customer 8, and without help it has only one option: read every row of the orders table and check each one. With 120 rows that takes no time. With 120 million rows, it takes long enough for users to give up. An index is a separate, sorted structure that lets the database jump straight to the matching rows instead, the way the index at the back of a book lets you find "foreign keys, page 214" without reading the whole book.

Indexes are the single most important tool for database performance. The right index can turn a query that takes seconds into one that takes a millisecond. But indexes aren't free: each one takes space and slows down every write. This lesson explains how indexes work, how to create them, how to check whether a query uses one, and how to decide which indexes a table needs.

How an index works

Most database indexes are B-trees: balanced trees whose leaves hold the indexed values in sorted order, each with a pointer back to its row. Because the values are sorted, the database can find any value by walking down a few levels of the tree instead of scanning everything. A B-tree over a million rows is only about four levels deep, so finding one value costs a handful of page reads instead of a million row checks.

Sorting is the key idea. An index on last_name helps with anything that benefits from names being in order:

  • Equality: WHERE last_name = 'Andersson'
  • Ranges: WHERE last_name BETWEEN 'A' AND 'C', WHERE price > 100
  • Prefix matches: WHERE last_name LIKE 'And%' (with conditions in SQLite, shown below)
  • Sorting: ORDER BY last_name can read the index in order instead of sorting
  • Joins: JOIN orders ON orders.customer_id = customers.id looks up each customer's orders

In SQLite, the table itself is also a B-tree, sorted by rowid. That's why looking up a row by its INTEGER PRIMARY KEY is always fast: the table is the index for that column. See Auto-Increment and Identity Columns.

EXPLAIN QUERY PLAN

Before creating indexes, you need a way to see what the database is doing. Put EXPLAIN QUERY PLAN in front of a query, and SQLite tells you its plan instead of running the query. The detail column is the part to read:

  • SCAN table means "read every row". On a big table, that's what you want to avoid.
  • SEARCH table USING INDEX name (column=?) means "jump to matching rows using an index".
  • USING COVERING INDEX means the index alone had every column the query needed, so the table wasn't touched at all.
  • USE TEMP B-TREE FOR ORDER BY means the results had to be sorted separately.

Here's a query on the shop's order_items table, before and after adding an index:

SQL Before and after an index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan says SCAN order_items: all 292 rows are checked. After CREATE INDEX, the plan says SEARCH order_items USING INDEX order_items_product_id_idx (product_id=?), and the database reads only the six matching rows. The query result is identical either way. Indexes never change what a query returns, only how fast it returns it.

(The id, parent and notused columns in the plan are internal numbers you can ignore. The Reading Query Plans lesson covers plans in full detail.)

Creating and dropping indexes

The syntax is short:

SQL
CREATE INDEX index_name ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX index_name ON table_name (column);
CREATE INDEX IF NOT EXISTS index_name ON table_name (column);
DROP INDEX index_name;
DROP INDEX IF EXISTS index_name;

Give indexes descriptive names. A common convention is table_columns_idx, such as orders_customer_id_idx. Index names must be unique across the whole database in SQLite and PostgreSQL (MySQL and SQL Server scope them per table, and drop them with DROP INDEX name ON table).

You don't need indexes for everything: the database creates some on its own. Every PRIMARY KEY and UNIQUE constraint gets an index automatically, because checking uniqueness requires a fast lookup. SQLite names these sqlite_autoindex_<table>_N:

SQL Automatic indexes from UNIQUE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The customers.email column is declared UNIQUE, so SQLite created sqlite_autoindex_customers_1 (origin u for "unique constraint"), and an email lookup is a SEARCH without any work from you.

Unique indexes

CREATE UNIQUE INDEX does two jobs at once: it speeds up lookups and enforces that no two rows share the indexed value. It's the way to add a uniqueness rule to an existing SQLite table, since SQLite's ALTER TABLE can't add constraints. This example fails on purpose:

SQL A unique index enforces a rule ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The insert stops with Error: UNIQUE constraint failed: products.name. If existing rows already contained duplicates, the CREATE UNIQUE INDEX itself would fail, so you'd clean up the duplicates first (the DISTINCT and GROUP BY lessons show how to find them).

Composite indexes

A composite index (multi-column index) sorts by its first column, then by the second within equal first values, and so on, just like a phone book sorted by last name, then first name. That ordering decides which queries it can help:

SQL A composite index and the leftmost prefix ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read the four plans:

  1. SEARCH โ€ฆ (customer_id=?): the index's first column alone is enough.
  2. SEARCH โ€ฆ (customer_id=? AND ordered_at>?): the database jumps to customer 8, then to March within that customer.
  3. SCAN orders USING COVERING INDEX โ€ฆ: there's no way to jump to a date when the index is sorted by customer first, so SQLite reads the whole index. (It reads the index rather than the table only because the index is smaller and holds every column this query needs.)
  4. SEARCH โ€ฆ with no USE TEMP B-TREE: within one customer, the index is already in date order, so no sort is needed.

This is the leftmost prefix rule: a composite index on (a, b, c) can be searched on a, on a and b, or on all three, but not on b or c alone. So put the column you filter by with = most often first, and the column you use for ranges or sorting after it. An index on (customer_id, ordered_at) also makes a separate index on customer_id alone redundant.

Many plans also say COVERING INDEX. Every index entry stores the rowid (and here the id column is the rowid), so when an index contains every column a query mentions, the table is never read. Covering indexes are explored further in Partial, Expression and Covering Indexes.

Indexes and joins

Joins are where indexes matter most, because a join without an index can mean scanning one table once for every row of the other. Index the columns you join on, which usually means every foreign key column (the database already indexes the primary key side):

SQL Indexing a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both steps are now a SEARCH: find customers in Poland with customers_country_idx, then find each customer's orders with orders_customer_id_idx. Delete the two CREATE INDEX lines and run it again: the plan changes to scanning orders and looking up each order's customer by primary key, which checks every order in the shop.

When an index can't be used

An index helps only when the database can use its sort order. These common patterns defeat it:

  • Wrapping the column in a function or calculation. WHERE lower(last_name) = 'andersson' or WHERE price * 1.2 > 100 can't search an index on the plain column, because the index is sorted by last_name, not lower(last_name). Write the condition on the bare column (WHERE price > 100 / 1.2), or create an index on the expression: CREATE INDEX โ€ฆ ON customers (lower(last_name));.
  • Leading wildcards. LIKE '%son' can't use sort order, because the beginning of the value is unknown.
  • Case-insensitive LIKE in SQLite. SQLite's LIKE ignores case by default, while a normal index is case-sensitive, so even LIKE 'And%' scans. An index declared COLLATE NOCASE lets SQLite search it.
  • Skipping the leftmost column of a composite index, as you saw above.
  • Low selectivity. If a condition matches most of the table (say status <> 'cancelled'), reading the whole table is faster than thousands of index lookups, and the planner rightly ignores the index.
SQL Functions on columns vs expression indexes ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first plan is a SCAN, the second a SEARCH โ€ฆ (<expr>=?) using the expression index. PostgreSQL supports expression indexes the same way; MySQL 8.0.13+ and SQL Server use functional key parts or indexed computed columns.

The cost of indexes

If indexes make reads fast, why not index every column? Because each index is a full sorted copy of its columns that must be kept up to date:

  • Every write gets slower. An INSERT into a table with five indexes writes six B-trees. UPDATEs of indexed columns and DELETEs pay too.
  • Storage grows. Indexes on a busy table can easily take more space than the table itself.
  • More choices for the planner. Redundant indexes add work without adding speed.

So index deliberately:

  1. Primary keys and UNIQUE columns are indexed for you.
  2. Index foreign key columns that you join on or delete parents by.
  3. Index columns that appear in frequent, slow WHERE, JOIN and ORDER BY clauses, ideally confirmed by looking at a query plan.
  4. Prefer one well-ordered composite index over several single-column ones when queries filter on the same columns together.
  5. Drop indexes that no query uses. PostgreSQL's pg_stat_user_indexes view shows how often each index is used.

Small tables (a few hundred rows) rarely need extra indexes at all: a scan is already instant. The examples here use small tables only so you can see the plans; the payoff appears as tables grow.

Managing existing indexes: list, rename, rebuild, disable

Creating an index is the easy part. Over a project's life you also need to find out which indexes exist, give them better names, repair or compact them, and switch one off to see whether anything misses it. The statements differ a lot between databases, so this section shows what runs in SQLite first and then the server equivalents.

Listing indexes

SQLite keeps every index in the sqlite_master catalog table, and two table-valued functions describe them: pragma_index_list(table) returns the indexes of a table, and pragma_index_info(index) returns the columns of one index. Joining them lists every index in the database with its columns:

SQL List every index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You'll see three rows: the automatic unique indexes on categories.name and customers.email (origin u, from the UNIQUE constraint) and the one you just created (origin c, from CREATE INDEX). Automatic indexes created by constraints can't be dropped with DROP INDEX; you change the constraint instead. The same job in the other databases:

Database List the indexes of orders
SQLite PRAGMA index_list('orders'); then PRAGMA index_info('index_name');
MySQL / MariaDB SHOW INDEX FROM orders; (or query information_schema.statistics)
PostgreSQL SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders'; (in psql: \d orders)
SQL Server EXEC sp_helpindex 'dbo.orders'; or join sys.indexes with sys.index_columns
Oracle SELECT index_name, uniqueness FROM user_indexes WHERE table_name = 'ORDERS'; plus user_ind_columns

Renaming an index

A name like idx1 tells the next person nothing; a convention such as table_columns_idx does. Most databases can rename in place, SQLite can't:

SQL
-- PostgreSQL and Oracle
ALTER INDEX orders_customer_idx RENAME TO orders_customer_id_idx;

-- MySQL 5.7+ and MariaDB 10.5.2+
ALTER TABLE orders RENAME INDEX orders_customer_idx TO orders_customer_id_idx;

-- SQL Server (note the table-qualified old name)
EXEC sp_rename N'dbo.orders.orders_customer_idx', N'orders_customer_id_idx', N'INDEX';

-- SQLite: drop and create again under the new name
DROP INDEX orders_customer_idx;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Renaming only changes the label. Anything that mentions the old name, such as SQL Server index hints or a script that runs DROP INDEX, has to be updated too.

Rebuilding and reindexing

An index can become bloated or fragmented after many updates and deletes, and rarely it can be corrupted. A rebuild writes a fresh, compact copy from the table's data. It also picks up a change of collation, which is why SQLite documents REINDEX after you change a custom collating sequence:

SQL Drop, recreate and REINDEX ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Only orders_customer_id_idx is left, and its stored definition is unchanged: REINDEX replaces the contents, not the definition. The server equivalents:

SQL
-- PostgreSQL (CONCURRENTLY avoids blocking writes, version 12+)
REINDEX INDEX CONCURRENTLY orders_customer_id_idx;
REINDEX TABLE orders;

-- SQL Server: REORGANIZE is a light online defragmentation, REBUILD recreates the index
ALTER INDEX orders_customer_id_idx ON dbo.orders REORGANIZE;
ALTER INDEX ALL ON dbo.orders REBUILD;

-- Oracle
ALTER INDEX orders_customer_id_idx REBUILD ONLINE;

-- MySQL (InnoDB has no REINDEX; rebuilding the table rebuilds its indexes)
ALTER TABLE orders ENGINE = InnoDB;
OPTIMIZE TABLE orders;

Don't rebuild on a schedule out of habit. A rebuild takes locks, writes a lot of log and cache-cold data; do it when you have measured fragmentation or a problem, not "just in case".

Disabling an index

Before dropping an index you suspect nobody needs, you'd like to hide it from the optimizer and watch. Support varies:

SQL
-- SQL Server: disabled indexes keep their definition but are not maintained or used
ALTER INDEX orders_customer_id_idx ON dbo.orders DISABLE;
ALTER INDEX orders_customer_id_idx ON dbo.orders REBUILD;   -- turns it back on

-- MySQL 8: an invisible index is still maintained, but the optimizer ignores it
ALTER TABLE orders ALTER INDEX orders_customer_id_idx INVISIBLE;
ALTER TABLE orders ALTER INDEX orders_customer_id_idx VISIBLE;

-- Oracle: invisible indexes behave the same way
ALTER INDEX orders_customer_id_idx INVISIBLE;

PostgreSQL and SQLite have no switch. PostgreSQL's DDL is transactional, so you can drop an index inside a transaction, run EXPLAIN on the queries you care about, and ROLLBACK (the drop takes a strong lock for as long as the transaction is open, so do it on a test copy). In SQLite you simply test on a copy that doesn't have the index.

Never disable a clustered index in SQL Server. The clustered index is the table's data, so disabling it makes the whole table inaccessible until you rebuild it.

When a unique index can't be created

A CREATE UNIQUE INDEX on a column that already has duplicate values fails, and nothing is changed. Find the offenders first, resolve them (merge, delete or correct), then create the index:

SQL Find the duplicates first ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Several cities have more than one customer, so CREATE UNIQUE INDEX customers_city_uq ON customers (city) would stop with UNIQUE constraint failed: customers.city. PostgreSQL's message names the duplicated value (Key (city)=(Lyon) is duplicated), MySQL reports Duplicate entry, and SQL Server says the index creation was terminated because a duplicate key was found. Remember that NULL handling differs too: SQLite, PostgreSQL and MySQL allow many NULLs in a unique index, while SQL Server allows only one.

Indexes in other databases (read-only)

The basic CREATE INDEX syntax is the same everywhere, but each database adds options:

SQL
-- PostgreSQL: build without blocking writes; other index types
CREATE INDEX CONCURRENTLY orders_customer_id_idx ON orders (customer_id);
CREATE INDEX products_tags_idx ON products USING gin (tags);

-- MySQL: indexes are often declared inside CREATE TABLE
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  ordered_at DATETIME NOT NULL,
  INDEX orders_customer_date_idx (customer_id, ordered_at)
);

-- SQL Server: clustered vs nonclustered, INCLUDE for covering columns
CREATE NONCLUSTERED INDEX orders_customer_idx
  ON orders (customer_id) INCLUDE (ordered_at, status);

PostgreSQL uses EXPLAIN (and EXPLAIN ANALYZE to actually run the query and time it), MySQL uses EXPLAIN, and SQL Server shows graphical execution plans. In MySQL's InnoDB and in SQL Server's clustered indexes, the table is stored in primary key order, much like SQLite's rowid tables.

Clustered and non-clustered indexes

So far, every index in this lesson has been a separate structure next to the table. That's only half the story. Databases also have to decide how the table itself is stored, and that choice creates two kinds of index:

  • A clustered index is the table. Its B-tree leaves hold the complete rows, sorted by the index key. Because rows can only be physically sorted one way, a table has at most one clustered index.
  • A non-clustered index (also called a secondary index) is a separate B-tree. Its leaves hold the indexed values plus a locator that points at the full row. A table can have many of them.

Think of a phone book: the book itself is sorted by last name, so the "last name index" is the book (clustered). An index of phone numbers printed at the back, each with a page reference, is non-clustered. Finding a person by name takes one lookup; finding one by number takes two: the back index, then the page.

That second step is the main cost of a non-clustered index. For every matching index entry, the database follows the locator to fetch the rest of the row (SQL Server calls it a key lookup, PostgreSQL a heap fetch). For a handful of rows that's cheap. For thousands of rows scattered over the table, those lookups can cost more than simply scanning everything, which is why the planner sometimes ignores an index that "should" help. A covering index avoids the second step entirely.

How each database stores tables

Database How the table is stored What a secondary index points to
SQLite (normal tables) Clustered by rowid (the INTEGER PRIMARY KEY, if you declare one) The rowid
SQLite (WITHOUT ROWID) Clustered by the declared PRIMARY KEY The primary key columns
MySQL / MariaDB (InnoDB) Always clustered by the primary key (or a hidden row id if there is none) The primary key columns
SQL Server Clustered by whatever you choose (the primary key by default), or an unsorted heap The clustering key, or a row id for heaps
PostgreSQL Always an unsorted heap; every index, including the primary key, is non-clustered A physical row address (ctid)
Oracle A heap by default; an index-organized table is clustered by its primary key A row address (ROWID), or the key for index-organized tables

Two consequences are worth remembering. First, in InnoDB and SQL Server, every secondary index contains the primary key, so a wide primary key (say, a 36-character UUID string) makes every index bigger. Second, in InnoDB, inserting rows in random key order (random UUIDs again) splits pages all over the clustered index, while increasing keys always append at the end. That's one reason auto-increment integers and time-ordered IDs (like UUIDv7) are popular primary keys.

Seeing the difference in SQLite

A SQLite table whose primary key isn't an INTEGER PRIMARY KEY is clustered by a hidden rowid, and the primary key gets a separate, non-clustered index. Adding WITHOUT ROWID makes the primary key the clustered index instead. Compare the plans:

SQL Rowid table vs WITHOUT ROWID

Press Run (or Ctrl + Enter) to see the result here.

For the rowid table, the plan says SEARCH countries_rowid USING INDEX sqlite_autoindex_countries_rowid_1 (code=?): find the code in the non-clustered index, then fetch the row by rowid. For the WITHOUT ROWID table it says SEARCH countries USING PRIMARY KEY (code=?): the row lives right in the primary key's B-tree, so one search is enough. The ORDER BY code plan is a plain SCAN countries with no USE TEMP B-TREE, because the table is already stored in code order. And sqlite_schema lists an automatic index only for countries_rowid; the WITHOUT ROWID table needs none.

What a secondary index stores

PRAGMA index_xinfo lists every column an index entry holds, including the hidden locator (marked with key = 0). Here, the same index on name is created on both tables:

SQL The locator inside a secondary index

Press Run (or Ctrl + Enter) to see the result here.

The first index stores name plus the rowid (shown as cid -1 with a NULL name). The second stores name plus code, the primary key, exactly like an InnoDB or SQL Server secondary index. That's why SELECT code ... WHERE name = 'Japan' is a COVERING INDEX search, while asking for population needs the second step into the table.

In practice, use WITHOUT ROWID in SQLite only for tables with a non-integer or composite primary key and fairly small rows, such as lookup tables and many-to-many junction tables. For everything else, a normal table with an INTEGER PRIMARY KEY is already clustered on that key.

The other databases make the choice in DDL (read-only):

SQL
-- SQL Server: pick the clustered index explicitly
CREATE TABLE order_lines (
  order_id INT NOT NULL,
  line_no INT NOT NULL,
  product_id INT NOT NULL,
  CONSTRAINT order_lines_pk PRIMARY KEY CLUSTERED (order_id, line_no)
);
CREATE NONCLUSTERED INDEX order_lines_product_idx ON order_lines (product_id);

-- MySQL (InnoDB): the primary key is always the clustered index
CREATE TABLE order_lines (
  order_id INT NOT NULL,
  line_no INT NOT NULL,
  product_id INT NOT NULL,
  PRIMARY KEY (order_id, line_no),
  INDEX order_lines_product_idx (product_id)
);

-- PostgreSQL: CLUSTER sorts the heap once by an index; new rows aren't kept in order
CLUSTER order_lines USING order_lines_pkey;

PostgreSQL's CLUSTER is a one-time rewrite that locks the table, so it's a maintenance tool rather than a permanent storage choice. Clustering pays off most for range queries on the clustering key: all lines of order 42 sit next to each other on the same pages, so WHERE order_id = 42 reads one or two pages instead of jumping around the table.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SCAN order_items in an EXPLAIN QUERY PLAN result mean?

    SCAN means a full pass over the table. SEARCH means the database jumps to matching rows with an index or the rowid.
  2. 2. A table has an index on (customer_id, ordered_at). Which WHERE clause can NOT search it?

    The index is sorted by customer_id first. Without a condition on the leftmost column, there's no place to jump to, so the database must scan.
  3. 3. Why might WHERE lower(email) = '[email protected]' be slow even though email has an index?

    Wrapping an indexed column in a function hides its sort order. Use an expression index on lower(email) or compare the bare column.
  4. 4. What is the main cost of adding many indexes to a table?

    Indexes never change results and usually speed up reads, but each one is maintained on every write.
  5. 5. Which column does NOT need an extra CREATE INDEX in most databases?

    PRIMARY KEY and UNIQUE constraints create their own index automatically. Foreign keys and filter columns need indexes you create yourself (MySQL's InnoDB indexes foreign keys automatically).

Summary

  • An index is a sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table.
  • Indexes help equality and range filters, prefix matches, ORDER BY and joins; they never change query results.
  • EXPLAIN QUERY PLAN shows SCAN (every row) vs SEARCH โ€ฆ USING INDEX (direct lookup); COVERING INDEX means the table wasn't touched.
  • CREATE [UNIQUE] INDEX name ON table (columns); PRIMARY KEY and UNIQUE constraints get indexes automatically.
  • Composite indexes follow the leftmost prefix rule: put equality columns first, then range or sort columns.
  • Functions on columns, leading wildcards and low-selectivity conditions prevent index use; expression indexes can help.
  • Manage indexes with PRAGMA index_list / SHOW INDEX / pg_indexes / sys.indexes; rename, REINDEX or REBUILD, and disable or hide them with the statements your database provides, and fix duplicates before CREATE UNIQUE INDEX.
  • Indexes cost write speed and storage: index foreign keys and proven hot queries, and drop unused indexes.

Next: Views, where you'll save queries as virtual tables.