Composite Index

A composite index covers multiple columns in a defined order.

Column order matters because SQLite uses leftmost-prefix matching.

Core Concepts

flowchart LR
  A[Index on (a, b, c)] --> B[Query on a]
  A --> C[Query on a, b]
  A --> D[Query on a, b, c]
  A --> E[Query on b only]
  B --> F[Can use index]
  C --> F
  D --> F
  E --> G[Often cannot use efficiently]

Composite index	Works best for
(customer_id, order_date)	Filters starting with customer_id
(category, created_at, status)	Queries filtering by category first

Good Match

-- Index supports filters beginning with customer_id.
CREATE INDEX idx_orders_customer_date
ON orders_comp(customer_id, order_date);

SELECT order_id, customer_id, order_date
FROM orders_comp
WHERE customer_id = 1
ORDER BY order_date;

Expected output
Query pattern aligns with leftmost index column.

Weak Match

-- This filter starts at second index column only.
SELECT order_id, customer_id, order_date
FROM orders_comp
WHERE order_date = '2026-03-02';

Expected output
Index may be less useful than expected due to column order mismatch.

Code Examples

-- Setup table for composite index example.
CREATE TABLE orders_comp (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  order_date   TEXT NOT NULL,
  total        REAL NOT NULL
);

INSERT INTO orders_comp (customer_id, order_date, total)
VALUES
  (1, '2026-03-01', 40.0),
  (1, '2026-03-02', 60.0),
  (2, '2026-03-02', 90.0),
  (2, '2026-03-03', 120.0);

Expected output
Table created and four rows inserted.

-- Create composite index.
CREATE INDEX idx_orders_customer_date
ON orders_comp(customer_id, order_date);

Expected output
Composite index created successfully.

-- Verify index columns and order.
PRAGMA index_info(idx_orders_customer_date);

seqno	cid	name
0	1	customer_id
1	2	order_date

SQLite-Specific Nuances

SQLite Nuance

In SQLite, composite index column order strongly affects usefulness.

Design index order to match your most frequent filter/sort prefix.

Common Pitfalls / Best Practices

Pitfall

Creating one composite index and assuming it optimizes every permutation of those columns.

Best Practice

Profile common query predicates and order composite columns by selectivity and access pattern.

Quick Challenge

Create a composite index on orders_comp(order_date, total) named idx_orders_date_total.

View Solution

CREATE INDEX idx_orders_date_total
ON orders_comp(order_date, total);

Expected output
Index created and available for date-first query patterns.