Indexes — Composite, Partial, Expression, and UNIQUE

The dataset for this article — the perf_sales table

An index works like the index in the back of a book: it lets you find rows with a specific column value quickly.

This article walks through creating indexes with CREATE INDEX, building composite, partial, expression, and UNIQUE indexes, and using EXPLAIN QUERY PLAN to confirm how the lookup strategy changes.

Before diving in, check the column definitions, a sample of the data, and the row count of perf_sales. (Run the queries correctly to reveal the explanation.)

① Use PRAGMA table_info(perf_sales); to view the column names, types, and primary key.

② Use SELECT COUNT(*) FROM perf_sales; to confirm the total row count.

③ Preview the first 5 rows with SELECT * FROM perf_sales LIMIT 5;. Since perf_sales has 50,000 rows, always include LIMIT.

CREATE INDEX and EXPLAIN QUERY PLAN — turn a full table scan into an index seek

Create an index with CREATE INDEX index_name ON table(column);.

When you use an indexed column in a WHERE equality or range condition, the optimizer can pick an index lookup instead of a full table scan and jump straight to the matching rows.

Check the lookup strategy with EXPLAIN QUERY PLAN.

Prefix your query with EXPLAIN QUERY PLAN and you'll see the plan for how the rows will be found instead of the actual results.

Without an index you'll see SCAN perf_sales (also called a FULL SCAN — examine every row in the table), and once an index kicks in you'll see something like SEARCH perf_sales USING INDEX ... (also called an INDEX SCAN — narrow the search with the index).

The exact wording of the plan varies by environment, so this article treats a successful run as the correct answer and reads the meaning of the plan in the prose and explanations.

-- Plan without an index (separate read-only example)
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales (examine every row)

-- Create an index, then look at the same plan
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- Note: run DROP -> CREATE -> EXPLAIN end-to-end in the same execution

Imagine the requirement: "We search sales for emp_id = 7 often, so we want to move from a full table scan to an index seek." You'll compare the plan and run time across three steps: ① measure the baseline without an index, ② build the index (a one-off cost that's not part of the pure comparison), and ③ re-measure with the index in place. Each console shows its total run time, so the key comparison is t1 (no index, this step) versus t3 (with index, step ③).

① In this first console, run SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; together with the matching EXPLAIN QUERY PLAN, then confirm SCAN perf_sales in the plan and note the total run time t1.

② In this second console, run DROP INDEX IF EXISTS ix; to clear any previous index, then CREATE INDEX ix ON perf_sales(emp_id); to build a fresh index on emp_id. This step is a one-off cost (building the index) and isn't counted in the pure search-speed comparison between t1 and t3.

③ In this third console, run the same SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7; and EXPLAIN QUERY PLAN as in step ①. Confirm that the plan now shows SEARCH perf_sales USING INDEX ix and that the total run time t3 is shorter than t1 from step ①.

Composite indexes — index multiple columns together

Writing several columns inside the parentheses — CREATE INDEX name ON table(col1, col2); — produces a composite index (also called a multi-column index).

It speeds up queries that use the conditions from the leading column onward, like WHERE col1 = ... AND col2 >= ....

Column order matters: a composite index won't help queries that don't use its leading column in the WHERE clause.

If your access pattern is fixed — say "equality on emp_id and a range on sale_date" — building a composite index in that order lets one index cover both conditions.

-- Composite index (separate read-only example)
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- Narrow by region (equality), then further narrow sale_date by range

Imagine the requirement: "We often pick a sales rep with emp_id and a period with sale_date, so we want one index that covers both conditions."

① Start with DROP INDEX IF EXISTS ix;.

② Create a composite index named ix on perf_sales listing emp_id and sale_date in that order.

③ Then run EXPLAIN QUERY PLAN on a query that filters by emp_id = 7 and sale_date >= '2024-01-01' and selects sale_id and amount, and confirm that the composite index is used.

Partial indexes and expression indexes

A partial index is built with CREATE INDEX name ON table(column) WHERE condition; and indexes only a subset of the rows in the table.

If you frequently search rows with a specific value such as status = 'pending', indexing only those rows keeps the index small and focused on the queries you actually run.

In perf_sales, only 5,000 rows (10% of the total) have status = 'pending', so a partial index with WHERE status = 'pending' is a natural fit.

An expression index is built with CREATE INDEX name ON table(expression); and indexes the result of a computed expression rather than a raw column.

Use it for queries that apply the same expression on the search side, such as WHERE amount / 100 = ....

A regular index on a column normally doesn't help once you apply a function or arithmetic to that column, but if you index the expression itself, searches using the matching expression can use it.

-- Partial index (separate read-only example)
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- Expression index
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

Imagine the requirement: "We often search pending sales (status = 'pending') by date range, so we want a small index that covers only those rows."

① Start with DROP INDEX IF EXISTS ix;.

② Create a partial index named ix on perf_sales(sale_date) that targets only rows where status = 'pending'.

③ Run EXPLAIN QUERY PLAN on a query that filters by status = 'pending' AND sale_date >= '2024-01-01' and confirm the partial index is used (SEARCH ... USING INDEX ix).

④ Then run EXPLAIN QUERY PLAN on a query that uses the same column but with status = 'paid' (for example, status = 'paid' AND sale_date >= '2024-01-01') and confirm that the partial index is not used (SCAN perf_sales) because the condition doesn't match the index's predicate.

Imagine the requirement: "We sometimes search by the bucket value of amount divided by 100 (amount / 100), so we want to index that computed result." Searches that apply arithmetic to a column don't benefit from a regular column index, so use an expression index instead.

① Start with DROP INDEX IF EXISTS ix;.

② Create an index named ix on the expression amount / 100 over perf_sales.

③ Run EXPLAIN QUERY PLAN on a query that filters by amount / 100 = 5000 and confirm the expression index is used (SEARCH ... USING INDEX ix).

④ Then run EXPLAIN QUERY PLAN on a query that filters by amount = 500000 without going through the expression (an example using the same amount column but with a different shape than the index) and confirm the expression index is not used (SCAN perf_sales).

UNIQUE indexes — an index that disallows duplicates

A UNIQUE index — built with CREATE UNIQUE INDEX name ON table(column); — speeds up searches and prevents duplicate values from being inserted into the column.

If you try to create a UNIQUE index on a column that already contains duplicates, you'll get a UNIQUE constraint failed error and the index won't be created at all.

The flip side: if you create one on a column whose values are unique (a primary key or a unique business key), you get duplicate prevention plus a fast equality lookup.

In perf_sales, product (200 distinct values) has many repeats, so a UNIQUE index isn't possible there. sale_id, however, is the primary key and unique, so you can build one.

The next exercise creates a UNIQUE index on sale_id, confirming that a unique column accepts the index and that equality lookups on that column use it.

-- UNIQUE index (separate read-only example)
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- Trying to add UNIQUE on a column with duplicates is rejected
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product has 200 distinct values repeated across 50,000 rows, so it can't be made UNIQUE

Imagine the requirement: "sale_id is unique per sale, so we want to prevent duplicates and speed up single-row lookups." This is the last exercise in the article.

① Start with DROP INDEX IF EXISTS ix;.

② Create a UNIQUE index named ix on the sale_id column of perf_sales (it works because sale_id is unique).

③ Then run EXPLAIN QUERY PLAN on a query that filters by sale_id = 12345 and selects sale_id and amount, and confirm that the single-row lookup goes through the index.

Tips — when to add an index

Rule of thumb: an index tends to beat a SCAN when you can narrow the result to about 15-20% or less of the table. Above that threshold, the optimizer often picks SCAN automatically anyway.

- Add one: equality or range searches on a column of a large table, primary keys / foreign keys / UNIQUE columns, JOIN keys, columns referenced by ORDER BY / GROUP BY

- Skip it: small tables with a few thousand rows or fewer, low-selectivity columns like gender, write-heavy tables, LIKE '%foo%' (a leading wildcard can't use an index)

The baseline workflow: check the SCAN / SEARCH plan with EXPLAIN QUERY PLAN and add indexes only to the slow queries.