Composite, Unique and Covering Indexes

Concept

A composite index covers several columns in a declared order. That order is not cosmetic - it decides which queries the index can serve at all.

The leftmost prefix rule

CREATE INDEX idx_emp_dept_status_salary
    ON employees (dept_id, status, salary);

Think of it as a phone book sorted by (surname, first name). You can find every "Nair", and every "Nair, Asha". You cannot find every "Asha" without reading the whole book.

Column order: the two rules

-- 1. Equality columns first, range columns last
--    This query filters status by equality and salary by range:
SELECT * FROM employees WHERE status = 'active' AND salary > 70000;

CREATE INDEX idx_good ON employees (status, salary);   -- equality, then range
CREATE INDEX idx_bad  ON employees (salary, status);   -- range first: status
                                                       -- cannot narrow anything

Once the index hits a range condition it can no longer use the following columns to narrow the search, only to filter what it already found. So every equality column must come before the first range column.

-- 2. Among equality columns, the more selective one usually goes first
SELECT COUNT(DISTINCT status)  / COUNT(*) AS status_selectivity,
       COUNT(DISTINCT dept_id) / COUNT(*) AS dept_selectivity
FROM   employees;

Selectivity is the fraction of distinct values. A column with two distinct values across a million rows is poor at narrowing anything; a near unique column is excellent. Higher is better, and the most selective equality column earns the leading position - though matching the order your queries actually use matters more than perfect selectivity ordering.

Covering indexes

CREATE INDEX idx_orders_cover ON orders (customer_id, order_date, total);

-- Every column this query needs is in the index. The table is never touched.
SELECT customer_id, order_date, total
FROM   orders
WHERE  customer_id = 1
ORDER BY order_date DESC;

-- EXPLAIN shows: Extra: Using index      <- an index only scan

Using index in the EXPLAIN output - not "using index condition" - means the query was answered entirely from the index, with no bookmark lookup into the table. On a wide table this is often a several fold improvement, and it is the strongest argument against SELECT *: a covering index cannot cover columns you did not need.

Unique indexes

CREATE UNIQUE INDEX uq_employees_email ON employees (email);

-- A composite unique index constrains the COMBINATION
CREATE UNIQUE INDEX uq_assignment ON assignments (employee_id, project_id);

A unique index does two jobs at once: it enforces the constraint and it speeds up lookups. It also tells the optimiser that at most one row can match, which lets it choose better plans.

Indexes and ORDER BY

-- The index is already in this order, so no sort step is needed
CREATE INDEX idx_orders_date ON orders (order_date DESC, id DESC);

SELECT id, order_date, total
FROM   orders
ORDER BY order_date DESC, id DESC
LIMIT 20;

Matching the index order to the ORDER BY removes the sort entirely - which is what makes deep pagination viable. Descending index support arrived in MySQL 8; earlier versions store ascending and read backwards.

Important rules

• A composite index serves any leftmost prefix of its columns, and nothing else.
• One index on (a, b) makes a separate index on (a) redundant - drop the narrower one.
• Put equality columns before range columns.
• A covering index eliminates the table lookup; check for Using index in EXPLAIN.
• MySQL usually uses one index per table per query; two single column indexes are not equivalent to one composite index.
• Index prefix length matters for long text: CREATE INDEX idx ON t (long_col(20)) indexes the first 20 characters.

Common mistakes

• Creating single column indexes on every column and expecting them to combine.
• Putting the range column first and wondering why the index barely helps.
• Keeping both (a) and (a, b) - the first is dead weight on every write.
• Assuming SELECT * can use a covering index.
• Choosing column order by selectivity alone, ignoring which prefixes queries actually use.

Best practices

• Design composite indexes from the query, not the table: list the WHERE equality columns, then the range column, then the ORDER BY columns.
• Aim for a small number of well chosen composite indexes rather than many single column ones.
• Add the selected columns to the index when it turns a hot query into an index only scan.
• Drop redundant prefixes and unused indexes.
• Verify every index decision with EXPLAIN.

Practice

1. Design the composite index for WHERE customer_id = ? AND status = ? ORDER BY order_date DESC.
2. Which of these can use INDEX (a, b, c): WHERE b = 1, WHERE a = 1 AND c = 3, WHERE a = 1 AND b = 2?
3. Turn a hot query on order_items into an index only scan and prove it with EXPLAIN.