Database indexing

This is the exact file agents read, including the credit header and licence notice. Save it as database-indexing/SKILL.md.

---
name: database-indexing
description: >
  Design, review and debug SQL indexes so queries run fast without slowing writes, across PostgreSQL, MySQL and MariaDB, SQLite, SQL Server and other engines: B-tree, composite, covering (INCLUDE), partial and expression indexes, with correct column order, selectivity judgement and write-cost trade-offs. Use it when someone asks to "add an index", "which index do I need", "speed up this WHERE or JOIN or ORDER BY", "my index is not being used", "the query still does a full table scan", "should this be a composite index", "what column order", "why is the planner ignoring my index", or when EXPLAIN shows a Seq Scan, type=ALL or SCAN on a large table. Do NOT use it to read a plan or find the bottleneck first (see query-optimization), for keyset pagination design (see efficient-pagination), for join logic (see sql-joins), or for window frame tuning (see window-functions).
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/database-indexing/.
---

# Database indexing

## When to use this, and when not to

Use this skill to decide whether a query needs an index, which columns to index and in what order, which index type fits, and whether an existing index earns its keep. The triggers are a slow lookup, join or sort on a large table, a request to "add an index", a composite-index column-order question, or a plan that still shows a sequential scan, `type=ALL` or `SCAN` when you expected the index to help.

Do not start here when the task is to read a plan or find the real bottleneck: run `query-optimization` first, because an index is only one of several fixes and often not the right one. Do not use this for cursor pagination design (`efficient-pagination`), for join structure (`sql-joins`), or for window frames (`window-functions`). The defining feature of this skill is that it changes the schema's access paths, not the query text.

An index is a workload-specific access path, not a tidy addition to a schema. Every index speeds some reads and slows every write to the table, so the job is to add the fewest indexes that serve the real queries.

## Inputs the agent must have before starting

1. The SQL engine and version. Column order rules are shared, but covering, partial, concurrent builds and specialised types differ (see the appendix).
2. The exact query or small set of queries the index must serve, with the `WHERE` predicates split into equality (`=`, `IN`) and range (`<`, `>`, `BETWEEN`, `LIKE 'x%'`), the `JOIN` keys, the `ORDER BY`, the `GROUP BY`, and the columns in the `SELECT` list.
3. The table size in rows, and roughly what fraction of the table the predicate returns (its selectivity).
4. The write rate: how often the table is inserted into, updated or deleted from, and whether the indexed columns change on update.
5. The existing indexes on the table, and whether this is a production or shared database.

If you have only the schema and not the query, stop and ask for the query. An index designed from column names alone is a guess. If selectivity is unknown, ask for a representative predicate value or run a `COUNT` for the common filter before designing.

## The workflow, step by step

### 1. Start from the query, never the schema

List the predicates the index must serve, tagged equality or range, plus the ordering. Design for the dominant query shape, not every possible filter. The same column can need different indexes for different workloads, so one query drives one decision.

### 2. Decide whether an index is warranted at all

An index helps when the predicate returns a small fraction of a large table. Decision rules:

- If the table is small (a few thousand rows that sit in a page or two), do not add an index. The engine will nearly always sequentially scan a one-page table whatever indexes exist, and that is correct.
- If the predicate matches most of the table (for example a boolean that is true for about 95% of rows), an ordinary index will not help, because reading the table in order is cheaper than jumping through the index and back to the heap. There is no single fixed cut-off; the planner decides from cost, but as the matched fraction rises past a large share of the table a sequential scan wins.
- If the matched fraction is small and the table is large, an index is likely to pay. Make the predicate selective with a composite or partial index if a single column is not selective on its own.

### 3. Pick the access method

Default to a B-tree. It serves equality, ranges, sorting and most ordinary lookups. Reach past it only for a reason:

- `jsonb`, array containment or full-text operators: GIN.
- Range and geometric or spatial operators: GiST.
- A very large, append-only table whose values track physical row order, such as time-series events: BRIN, which is far smaller than a B-tree.
- Equality only, on an engine where it is supported and measured faster: hash, rarely.

Do not choose a type from the column's data type alone. The operator in the query decides.

### 4. Set the column order for a composite index (the rule most queries get wrong)

Column order is the main design choice, because a composite index serves any leftmost prefix of its columns and nothing else. An index on `(a, b, c)` serves searches on `a`, on `(a, b)` and on `(a, b, c)`, but not on `b` alone or `c` alone.

Decision rule for the order:

1. Put the equality columns first. These narrow the scan the most, and their relative order among themselves matters mainly for which shorter queries the prefix can also serve, so lead with the column the most queries filter on.
2. After the equality columns, add at most one range or sort column. Only the first inequality column narrows the scanned portion of the index; any column after a range is checked inside the index but does not reduce how much of it is read. So a second range column buys little.
3. Do not order columns by cardinality or by how they appear in the query. "Most selective column first" is not the rule; order by how the columns are used across your queries.

For `WHERE tenant_id = ? AND status = ? ORDER BY created_at DESC`, the index is `(tenant_id, status, created_at)`: two equality columns, then the sort column.

### 5. Match ORDER BY to skip the sort

If the index columns match the `ORDER BY` after the equality columns, the engine returns rows already ordered and skips a separate sort, which matters most with `LIMIT`, because it can then stop early. Mixed directions (`ORDER BY a ASC, b DESC`) need an index whose column directions match, or the sort stays.

### 6. Add covering columns only when a hot read needs them

If a frequent query selects just a few columns, add them so the index answers the query without touching the table (an index-only scan, shown as `Using index` in MySQL or `USING COVERING INDEX` in SQLite). Put search and sort columns in the key; put columns that are only returned in `INCLUDE` where the engine supports it. Do not add every selected column, and do not include large text or `jsonb` values: a bloated index costs storage and slows writes and often loses its advantage.

### 7. Use a partial index when queries always filter a subset

If the queries only ever touch a slice of the table (`WHERE status = 'OPEN'`, `WHERE deleted_at IS NULL`), index only that slice with a `WHERE` clause on the index. It is smaller, faster to maintain and cheaper on writes. The query predicate must match the index predicate for the engine to use it.

### 8. Use an expression index for a predicate you cannot rewrite

If queries always filter on a deterministic expression such as `lower(email)` and you cannot normalise the data at write time, index the expression. Prefer canonicalising at write time where the domain allows it, because it keeps queries simple and avoids a second write path.

### 9. Weigh the write and storage cost before creating

Every extra index increases insert cost, increases update cost when the indexed columns change, increases delete cost, and adds storage and maintenance work. On a high-write table, require stronger evidence before adding one: an index that makes a rare dashboard query 50 ms faster is a poor trade if it slows millions of inserts. Before adding, check for a duplicate or a redundant prefix: if `(a, b)` exists, a new `(a)` is usually redundant.

### 10. Build safely, then verify the planner uses it

On a production table, do not build a blocking index. Use `CREATE INDEX CONCURRENTLY` on PostgreSQL (it cannot run inside a transaction block), an online or in-place build on MySQL, or `ONLINE = ON` on SQL Server. Creating and dropping indexes changes the schema, so treat the DDL as an irreversible action: show the statement, state the lock and build-time impact, and get explicit human confirmation before running it on a shared or production database. Default to producing the statement, not executing it.

After building, confirm with `EXPLAIN` that the planner actually uses the index and that the sort or sequential scan is gone, then re-measure. If the planner still ignores it, do not force it: check that statistics are current (`ANALYZE`), that the predicate is not wrapped in a function (see failure mode 3), and that the predicate is selective enough to beat a scan.

## Worked example, end to end

An `orders` table has 50 million rows. The hot query, run thousands of times a minute, is:

```sql
SELECT id, total
FROM orders
WHERE customer_id = :cid
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;
```

Before any suitable index, `EXPLAIN` shows a sequential scan that reads far more rows than it returns, with most discarded by a filter and a separate sort for the `ORDER BY`:

```
Limit  (cost=... rows=20)
  ->  Sort  (Sort Key: created_at DESC)
        ->  Seq Scan on orders  (rows=50000000)
              Filter: (customer_id = :cid AND status = 'paid')
              Rows Removed by Filter: 49984000
```

Apply the rule. Equality columns first (`customer_id`, then `status`), then the sort column `created_at` descending:

```sql
CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);
```

Now the plan is an index scan that reads only this customer's paid orders, already in date order, and the `LIMIT` stops after 20 rows with no sort:

```
Limit  (cost=... rows=20)
  ->  Index Scan using idx_orders_cust_status_created on orders
        Index Cond: (customer_id = :cid AND status = 'paid')
```

To remove the final table lookups for `id` and `total`, make it covering:

```sql
-- PostgreSQL / SQL Server
CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC) INCLUDE (total);
```

One caution: if another frequent query filters on `customer_id` alone, this one index already serves it through the leftmost prefix, so do not add a second `(customer_id)` index. If a query filters on `status` alone, this index cannot serve it, because `status` is not a leftmost prefix.

## Templates you can use verbatim

```sql
-- Composite: equality columns, then one range or sort column
CREATE INDEX idx_t_a_b_created ON t (a, b, created_at DESC);

-- Covering (PostgreSQL / SQL Server): search keys, then returned-only columns
CREATE INDEX idx_t_a_b ON t (a, b) INCLUDE (c, d);

-- Partial: index only the slice the queries touch
CREATE INDEX idx_t_open ON t (tenant_id, created_at) WHERE status = 'OPEN';

-- Expression: index a deterministic transform you filter on
CREATE INDEX idx_t_lower_email ON t (lower(email));

-- Non-blocking build on a live table (PostgreSQL; not inside a transaction)
CREATE INDEX CONCURRENTLY idx_t_a ON t (a);

-- Check which indexes a query uses, and whether an index is used at all
EXPLAIN SELECT ...;                       -- read the plan, see Index Scan vs Seq Scan
-- PostgreSQL: index usage counts
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 't';
-- MySQL: list indexes on a table
SHOW INDEX FROM t;
-- SQLite: SEARCH ... USING INDEX means the index is used
EXPLAIN QUERY PLAN SELECT ...;
```

## Failure modes and anti-patterns

1. **Indexing by cardinality, or indexing every column in a WHERE clause.** Spot it when a table has many overlapping single-column indexes and writes have slowed. Fix: design from the real query shapes, one composite index per dominant shape, and order the columns by usage, not cardinality.
2. **Wrong column order, so the index cannot serve the real predicate.** Spot it when the index exists but `EXPLAIN` still shows a sequential scan or `type=ALL`, often because the query filters on a column that is not a leftmost prefix. Fix: reorder with equality columns first and the most-queried column leading, since a two-column index does not support searching the second column alone.
3. **A non-sargable predicate defeats the index.** Spot it when the column is wrapped in a function or cast (`WHERE lower(email) = ...`, `WHERE date(created_at) = ...`, `WHERE id::text LIKE ...`) or the pattern has a leading wildcard (`LIKE '%x'`), and the plan ignores the index. Fix: rewrite so the column is bare (`created_at >= :d AND created_at < :d + 1`), cast the constant not the column, add an expression index, or use a trigram index for wildcard search. Coordinate with `query-optimization` for the rewrite.
4. **Redundant, duplicate or oversized indexes.** Spot it when `(a)` and `(a, b)` both exist, or an `INCLUDE` carries large columns. Fix: drop the index whose columns are a prefix of another once you have confirmed it is unused, and include only the columns the hot query returns.
5. **Dropping or building on production without care.** Spot it when an index "looks unused" over a short window, or a `CREATE INDEX` locks writes on a live table. Fix: confirm usage across a full business cycle before dropping, never drop a unique index casually because it may enforce a data invariant, build with a concurrent or online method, and require human confirmation for any index DDL on a shared database.

## Quality bar

- The index serves a named, real query, not a column that merely appears in a `WHERE` clause.
- Column order is verified: equality columns first, at most one range or sort column after them, and the leftmost column is the one the most queries filter on.
- `EXPLAIN` confirms the planner uses the index and that the separate sort or sequential scan is gone, and the query was re-measured after building.
- Selectivity justifies the index: the predicate returns a small fraction of a large table.
- The write and storage cost was weighed, and no duplicate or redundant prefix index was left in place.
- The build method suits the environment (concurrent or online on production), and any `CREATE` or `DROP` was confirmed with a human before running on a shared database.

## Data handling and safety

Index design uses column names, the query shape and row-count estimates, not the data values, so refer to columns by name and never paste real personal, health or financial values into prompts or shared logs. Creating or dropping an index is a schema change: a `DROP INDEX` can remove a uniqueness guarantee or the only fast path for a rare but important query, and a blocking `CREATE INDEX` can lock out writes. Treat both as irreversible actions. Show the statement, state the lock and build-time impact, and require explicit human confirmation before running it against a shared or production database. On PostgreSQL, a failed `CREATE INDEX CONCURRENTLY` can leave an `INVALID` index behind; check for it and drop it before retrying.

## Appendix: engine-specific notes (verify before relying on these)

- **PostgreSQL**: supports `INCLUDE` covering columns, partial indexes (`WHERE`), expression indexes, and `CREATE INDEX CONCURRENTLY` (outside a transaction block). Multicolumn support covers B-tree, GiST, GIN and BRIN, up to 32 columns, and the documentation advises using multicolumn indexes sparingly and that indexes beyond three columns are rarely helpful. An index-only scan needs the table's visibility map reasonably current, so a recently vacuumed table. PostgreSQL 18 adds B-tree skip scan, which can use an index even without a leading-column constraint when the leading column has few distinct values.
- **MySQL and MariaDB (InnoDB)**: the primary key is clustered, so every secondary index carries the primary key columns, which is why a short primary key matters. Leftmost-prefix applies. `Using index` in `EXPLAIN` means a covering index. Long text columns can use a prefix index (`col(20)`). There are no partial indexes before recent versions; emulate with a generated column plus an index. Online DDL uses `ALGORITHM=INPLACE` or `INSTANT`; invisible indexes (8.0+) let you test dropping an index safely.
- **SQLite**: supports partial, expression and covering indexes. `EXPLAIN QUERY PLAN` shows `SEARCH ... USING INDEX` when an index narrows the scan, `USING COVERING INDEX` when the table is not read, and `SCAN` for a full scan; a `USE TEMP B-TREE FOR ORDER BY` line means the sort was not served by an index. Its output format is for interactive debugging and can change between releases.
- **SQL Server**: supports `INCLUDE` covering columns, filtered indexes (`WHERE`), `ONLINE = ON` builds, and the clustered versus nonclustered distinction, where the clustered index is the table's row storage.

## Sources

- PostgreSQL 18 documentation, "Multicolumn Indexes" and "Indexes": the exact rule that equality constraints on leading columns plus one inequality on the first non-equality column limit the scanned portion, the sparing-use guidance, and index overhead on writes. Verified 30 October 2026.
- MySQL 8.4 Reference Manual, "How MySQL Uses Indexes": the leftmost-prefix rule for `(col1, col2, col3)`, covering indexes, selecting the most selective index, and when indexes are not used. Verified 30 October 2026.
- Use The Index, Luke (Markus Winand), "Concatenated Keys": order composite columns by usage not selectivity, lead with the commonly filtered column, and that fewer indexes mean faster writes. Verified 30 October 2026.
- SQLite documentation, "EXPLAIN QUERY PLAN": `SCAN` versus `SEARCH`, `USING INDEX` and `USING COVERING INDEX`, and `USE TEMP B-TREE` for an unindexed sort. Verified 30 October 2026.
- PostgreSQL documentation, "Using EXPLAIN": confirming an index scan replaces a sequential scan, and that estimated rows far above returned rows signal a plan to fix. Verified 30 October 2026.