Composite Index & Covering Index

Composite Index

An index on multiple columns together — not separate indexes on each column.

-- Single column indexes (separate)
CREATE INDEX idx_last ON users(last_name);
CREATE INDEX idx_first ON users(first_name);

-- Composite index (together) -- VERY DIFFERENT
CREATE INDEX idx_name ON users(last_name, first_name);

Stored as: sorted first by last_name, then by first_name within same last_name. Like a phone book.

The Leftmost Prefix Rule

Composite index can only be used if query starts from the leftmost column.

-- Index on (last_name, first_name, age)
SELECT * WHERE last_name = 'Smith'                          -- Uses index
SELECT * WHERE last_name = 'Smith' AND first_name = 'John'  -- Uses index
SELECT * WHERE first_name = 'John'                          -- CANNOT use index
SELECT * WHERE age = 30                                     -- CANNOT use index

Column Order Matters

• Put HIGH cardinality columns first
• Put EQUALITY columns before RANGE columns
• Range condition (>, <, BETWEEN) stops index usage for subsequent columns

Covering Index

An index that contains all columns a query needs — so the query never touches the actual table.

-- Query needs: id, email, age
SELECT id, email, age FROM users WHERE email = 'bob@gmail.com'

-- Covering index on (email, id, age)
-- Leaf node already has: email + id + age
-- No need to go back to the table at all (index only scan)

How to Spot a Covering Index Opportunity

SELECT col_a, col_b     <- these need to be in index
FROM table
WHERE col_c = x          <- this must be leftmost in index
AND   col_d = y           <- this too

Covering index: (col_c, col_d, col_a, col_b)
WHERE columns first → SELECT columns after

Composite vs Covering — Key Difference

	Composite Index	Covering Index
Definition	Index on multiple columns	Index containing all query columns
Goal	Speed up multi-column filters	Eliminate table lookup entirely
Table access	May still need row fetch	Never touches table
Extra storage	Medium	Higher (more columns stored)
Best for	WHERE / JOIN / ORDER BY	High-frequency SELECT queries

Every covering index IS a composite index. Not every composite index IS a covering index.