Skip to main content

What is a composite index?

SQLMiddle~1 min read
Suggest an edit

A composite index is an index built on several columns at once. It helps speed up queries that filter or sort by a combination of those fields.

Example

sql
CREATE INDEX idx_users_city_age ON users(city, age);

Such an index is useful for queries like:

sql
SELECT * FROM users WHERE city = 'Kyiv' AND age > 25;

How it works

  • The index stores rows sorted first by city, then by age.
  • The database can quickly find every user from Kyiv, then quickly filter them by age.

The order rule (leftmost prefix rule)

This index gets used when the query involves:

  • only the first field (city),
  • or the first plus the second (city, age), but not just the second field (age) alone, if the first (city) isn't used.

Where it applies

Composite indexes are especially effective when:

  • WHERE filters often cover several columns;
  • queries sort or group by several fields (ORDER BY city, age).

Summary: A composite index is an ordered "multi-level table of contents" that speeds up searches on a combination of fields, but is sensitive to their order.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit