Composite, covering, unique and partial indexes

Composite indexes and column order

A composite (multi-column) index sorts by the first column, then by the second within each first value, and so on. An index on employees(dept_id, salary) lists all of department 1 sorted by salary, then all of department 2 sorted by salary, and so on.

This gives the leftmost-prefix rule: an index on (a, b, c) can help queries that filter on a, on a and b, or on a, b and c, but usually not on b or c alone. SQLite's own composite key on order_items(order_id, product_id) shows it:

WHERE order_id = 101    ->  SEARCH order_items USING INDEX sqlite_autoindex_order_items_1 (order_id=?)
WHERE product_id = 2    ->  SCAN order_items

How to choose the order: put columns tested with = first, then the one used for a range (>, <, BETWEEN) or for sorting. With employees(dept_id, salary), WHERE dept_id = 1 AND salary > 90000 is one quick search, and WHERE dept_id = 2 ORDER BY salary needs no extra sort, because the rows come out of the index already in salary order.

CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);
SELECT seqno, name FROM pragma_index_info('idx_emp_dept_salary');
-- 0 | dept_id
-- 1 | salary

Covering indexes

Normally the database finds entries in the index, then follows each pointer to the table to fetch the other columns. If the index already contains every column the query needs, that second step disappears. Such an index is called covering for that query. SQLite reports USING COVERING INDEX; PostgreSQL shows an Index Only Scan.

SELECT dept_id, salary FROM employees WHERE dept_id = 2;
SEARCH employees USING COVERING INDEX idx_emp_dept_salary (dept_id=?)

PostgreSQL (11 and later) and SQL Server let you add non-key columns just for covering: CREATE INDEX ... ON orders (customer_id) INCLUDE (order_date, status). The INCLUDE columns are stored in the index but not used for sorting.

Unique indexes

CREATE UNIQUE INDEX both speeds up lookups and enforces a rule: no two rows may share the value. It is how you add uniqueness to an existing table, since SQLite's ALTER TABLE cannot add a UNIQUE constraint. NULLs do not count as equal in SQLite, PostgreSQL or MySQL, so several rows may have a NULL email. SQL Server is different: its unique indexes allow only one NULL.

CREATE UNIQUE INDEX idx_customers_email ON customers(email);
INSERT INTO customers (id, name, city, email, joined)
VALUES (7, 'Asha V', 'Pune', 'asha@example.com', '2025-05-01');
-- Error: UNIQUE constraint failed: customers.email
INSERT OR IGNORE INTO customers (id, name, city, email, joined)
VALUES (7, 'Asha V', 'Pune', 'asha@example.com', '2025-05-01');
-- no error, and no row added

Partial indexes

A partial index covers only the rows that match a WHERE condition. If the warehouse screen always asks for orders still in Placed status, and those are a small share of all orders, index just them. The index is small, cheap to maintain, and very selective. SQLite and PostgreSQL support partial indexes; SQL Server calls them filtered indexes; MySQL does not have them.

CREATE INDEX idx_orders_placed ON orders(order_date) WHERE status = 'Placed';
SELECT name, partial FROM pragma_index_list('orders');
-- idx_orders_placed | 1

A partial unique index is the standard trick for soft deletes (module 9): CREATE UNIQUE INDEX ... ON users(email) WHERE deleted_at IS NULL keeps emails unique among live users while letting a deleted account's email be reused.

Expression indexes

Users type emails in any case. If you search with WHERE lower(email) = 'asha@example.com', a normal index on email cannot help, because the index holds the original values. An index on the expression itself can: CREATE INDEX idx_customers_email_lower ON customers(lower(email)). The query must use exactly the same expression. PostgreSQL supports this too; MySQL 8.0.13 and later supports it as a functional key part, written with extra brackets: ((lower(email))).

Common mistakes

• Wrong column order: an index on (salary, dept_id) for queries that filter on dept_id. Fix: equality columns first.
• Separate single-column indexes on a and b, expecting them to act like one (a, b) index. Fix: create the composite index the query needs.
• A partial index the query cannot use, because the query's WHERE does not repeat the index condition. Fix: match the condition exactly.
• Searching lower(email) with only a plain email index. Fix: an expression index, or store emails in lower case.
• Adding a composite index whose leading column is already a separate index you no longer need. Fix: (a, b) also serves queries on a alone, so drop the redundant (a) index.