SQL Indexes: How They Work and When to Use Them

Take SELECT * FROM users WHERE email = 'x@example.com'. Without an index on email, the database has to read every row and compare its email: a full table scan. With an index, it looks the value up in a sorted structure and jumps to the one matching row: an index seek.

The Playground is a SQLite database, and you can see which indexes it has by querying its catalogue table, sqlite_master:

SELECT name AS index_name, tbl_name AS table_name
FROM sqlite_master
WHERE type = 'index'
ORDER BY tbl_name;

▶ Run this query in the SQL Playground

There are 4, and nobody wrote CREATE INDEX for them. SQLite created them automatically for the UNIQUE columns: users.email, employees.email, products.sku and payments.transaction_id. Every database does this, because enforcing "no duplicates" needs a fast way to look a value up. (The INTEGER PRIMARY KEY columns need no separate index in SQLite: the table itself is stored in id order.)

Most indexes are B-trees: balanced trees whose leaves hold the indexed values in sorted order, each with a pointer to its row. To find a value, the database starts at the root and at each level picks the branch whose range contains it. Even for millions of rows that is only three or four levels.

Root:              [ ... | M | ... ]
                    /               \
Branch:   [ A ... F | G ... L ]   [ M ... R | S ... Z ]
               |                        |
Leaves:   values A..F, each        values M..R, each
          with a pointer to         with a pointer to
          its table row             its table row

Because the leaves are sorted, a B-tree helps with more than equality: ranges (BETWEEN, >), prefixes (LIKE 'abc%'), sorting (ORDER BY) and MIN/MAX. Hash indexes, available in some databases, only help with equality.

-- a single-column index
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- a unique index: also prevents duplicates
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- a composite (multi-column) index
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- remove an index
DROP INDEX idx_orders_user_id;            -- SQLite, PostgreSQL, Oracle
DROP INDEX idx_orders_user_id ON orders;  -- MySQL, SQL Server

A common naming convention is idx_<table>_<columns>. Index names must be unique within a schema (in SQL Server and MySQL, within a table).

Clustered vs non-clustered

PostgreSQL has no permanent clustered index: tables are stored as heaps and every index is secondary. A table in SQL Server without a clustered index is also a heap.

Unique index

A unique index both speeds up lookups and rejects duplicates. That is what the automatic Playground indexes do. You can check that the indexed column really is unique:

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT email) AS distinct_emails
FROM users;

▶ Run this query in the SQL Playground

Composite index and the leftmost-prefix rule

A composite index on (user_id, order_date) is sorted by user_id first, then by order_date within each user, like a phone book sorted by last name, then first name. It can be used for:

So column order matters. Put the column that is always filtered with = first and a range or sort column after it.

Covering index

An index covers a query when it contains every column the query needs. The database can then answer from the index alone, without visiting the table. SELECT order_date FROM orders WHERE user_id = 4 is covered by an index on (user_id, order_date). SQL Server and PostgreSQL 11+ can add extra non-key columns with INCLUDE (...).

Partial (filtered) and expression indexes

-- only index the rows you query
CREATE INDEX idx_orders_open ON orders (order_date)
WHERE status IN ('pending', 'processing');   -- SQLite, PostgreSQL; SQL Server: filtered index

-- index the result of an expression
CREATE INDEX idx_users_email_lower ON users (LOWER(email));  -- SQLite, PostgreSQL, Oracle, MySQL 8.0.13+

An expression index lets WHERE LOWER(email) = '...' use an index; a plain index on email cannot help that query.

Each database has its own catalogue. In SQLite, pragma_index_list lists a table's indexes and pragma_index_info their columns. Both can be queried with SELECT:

SELECT il.name AS index_name, il."unique" AS is_unique, ii.name AS column_name
FROM pragma_index_list('products') il
JOIN pragma_index_info(il.name) ii;

▶ Run this query in the SQL Playground

EXPLAIN shows how the database intends to run a query, including whether it uses an index. The command differs: EXPLAIN QUERY PLAN in SQLite, EXPLAIN or EXPLAIN ANALYZE in PostgreSQL and MySQL, and the graphical or SET SHOWPLAN plan in SQL Server. In SQLite the output for an email lookup looks like this:

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'x@example.com';
-- SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE city = 'Austin';
-- SCAN users

The optimizer chooses by estimated cost. An index existing does not mean it is used: if a filter matches most of the table, reading the table directly is cheaper, and the optimizer will skip the index.

An index helps most when a filter on the column narrows the rows a lot. A rough measure is selectivity: distinct values divided by rows. Close to 1 is highly selective; close to 0 is not. Here it is for some Playground columns:

SELECT 'users.email' AS column_name, COUNT(DISTINCT email) AS distinct_values, COUNT(*) AS rows,
       ROUND(1.0 * COUNT(DISTINCT email) / COUNT(*), 3) AS selectivity FROM users
UNION ALL
SELECT 'users.city', COUNT(DISTINCT city), COUNT(*), ROUND(1.0 * COUNT(DISTINCT city) / COUNT(*), 3) FROM users
UNION ALL
SELECT 'users.is_premium', COUNT(DISTINCT is_premium), COUNT(*), ROUND(1.0 * COUNT(DISTINCT is_premium) / COUNT(*), 3) FROM users
UNION ALL
SELECT 'orders.user_id', COUNT(DISTINCT user_id), COUNT(*), ROUND(1.0 * COUNT(DISTINCT user_id) / COUNT(*), 3) FROM orders
UNION ALL
SELECT 'orders.status', COUNT(DISTINCT status), COUNT(*), ROUND(1.0 * COUNT(DISTINCT status) / COUNT(*), 3) FROM orders;

▶ Run this query in the SQL Playground

Email is unique, so a lookup by email finds one row: an ideal index. is_premium has two values, so a filter on it still matches about half the table and an index on it alone rarely helps. Selectivity is only a guide: an index on a low-selectivity column can still pay off when you filter on the rare value, for example the few orders still pending, and a partial index is often the better tool for that.

On real tables of a few hundred rows, like the Playground's, a full scan is already instant and indexes make no measurable difference. They matter from tens of thousands of rows upwards.

Foreign-key columns such as orders.user_id, order_items.order_id and reviews.product_id are the classic candidates, because they are joined on constantly. Most databases do not index foreign keys automatically (MySQL InnoDB is the exception).

The date rewrite is worth seeing on real data. Both queries count 2024 orders; the second compares the column directly, so an index on order_date could serve it:

SELECT
  (SELECT COUNT(*) FROM orders WHERE strftime('%Y', order_date) = '2024') AS with_function,
  (SELECT COUNT(*) FROM orders
   WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01') AS with_range;

▶ Run this query in the SQL Playground

Every index is a copy of some columns that has to be kept in sync. Each INSERT adds an entry to every index on the table; each UPDATE of an indexed column moves an entry; each DELETE removes one. Indexes also take disk space and memory.

Over time, many updates can leave an index fragmented. SQL Server has ALTER INDEX ... REBUILD / REORGANIZE, PostgreSQL REINDEX, SQLite REINDEX and VACUUM. Keeping table statistics fresh (ANALYZE) matters as much, because the optimizer relies on them.

Six errors and how to avoid them.

CREATE INDEX i1 ON orders (status);
CREATE INDEX i2 ON orders (discount);
CREATE INDEX i3 ON orders (item_count);
-- ...one per column

-- the columns real queries filter, join and sort on
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

CREATE INDEX idx ON orders (order_date, user_id);
-- query: WHERE user_id = ? ORDER BY order_date

CREATE INDEX idx ON orders (user_id, order_date);

WHERE strftime('%Y', order_date) = '2024'

WHERE order_date >= '2024-01-01'
  AND order_date <  '2025-01-01'

CREATE INDEX idx ON users (is_premium);

CREATE INDEX idx ON users (is_premium, created_at);
-- or a partial index on the rare value

-- no index on orders.user_id
SELECT ... FROM users u JOIN orders o ON o.user_id = u.id

CREATE INDEX idx_orders_user_id ON orders (user_id);

-- "I added an index, so it must be fast now"

EXPLAIN QUERY PLAN SELECT ...;  -- SQLite
EXPLAIN ANALYZE SELECT ...;     -- PostgreSQL

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List every index in the Playground database together with the SQL that created it. Table: sqlite_master

   Show solution

   SQLite · runs in the Playground

   SELECT name, tbl_name, sql
   FROM sqlite_master
   WHERE type = 'index';

   ▶ Run this query in the SQL Playground

   Automatic indexes have no SQL (NULL), because SQLite created them for UNIQUE constraints rather than from a CREATE INDEX statement.

2. 2Easy Work out the selectivity of orders.status and orders.shipping_city. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT ROUND(1.0 * COUNT(DISTINCT status) / COUNT(*), 3) AS status_selectivity,
          ROUND(1.0 * COUNT(DISTINCT shipping_city) / COUNT(*), 3) AS city_selectivity
   FROM orders;

   ▶ Run this query in the SQL Playground

   Both are low: few distinct values for many rows.

3. 3Medium Rewrite this filter so an index on order_date could be used, and check that the count is unchanged: WHERE strftime('%Y-%m', order_date) = '2024-06'. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS june_2024_orders
   FROM orders
   WHERE order_date >= '2024-06-01'
     AND order_date <  '2024-07-01';

   ▶ Run this query in the SQL Playground

   A half-open range on the column itself is index-friendly and matches the same dates.

4. 4Medium Find how many orders each customer has, to judge whether an index on orders.user_id would narrow lookups well. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT MIN(orders) AS fewest, MAX(orders) AS most, ROUND(AVG(orders), 2) AS average
   FROM (SELECT user_id, COUNT(*) AS orders
         FROM orders
         GROUP BY user_id) t;

   ▶ Run this query in the SQL Playground

   A lookup by user_id finds only a handful of rows, so an index on it is selective.

5. 5Medium List the indexes on the payments table and whether each one is unique. Table: pragma_index_list

   Show solution

   SQLite · runs in the Playground

   SELECT name, "unique" AS is_unique, origin
   FROM pragma_index_list('payments');

   ▶ Run this query in the SQL Playground

   origin is "u" for an index created by a UNIQUE constraint and "c" for CREATE INDEX.