Skip to content
Optimal Lab

Databases · interactive

Composite indexes and column order

An index on (country, city, age) is sorted by country, then city within each country, then age. Like a phone book sorted by surname then first name: great for some questions, useless for others.

SELECT * FROM users WHERE country = 'PK' AND city = 'Lahore'

INDEX ON users (country, city, age)

  1. DEBerlin29
  2. DEBerlin41
  3. DEMunich35
  4. PKKarachi22
  5. PKKarachi38
  6. PKLahore19match
  7. PKLahore27match
  8. PKLahore33match
  9. PKLahore45match
  10. UKLeeds31
  11. UKLondon26
  12. UKLondon52

index columns used: country, city · entries read 4 · matches 4

Equality on the leading column(s): one seek to a contiguous slice of the index.

WHERE

Step 2 of 4

  1. 1

    Sorted by the first column, then the next

    Entries with the same country sit together, and within them the same city, and so on.

    The leftmost-prefix rule.

  2. 2

    Use any leftmost prefix

    (country), (country, city) and (country, city, age) all seek straight to a slice.

    One index serves several queries.

  3. 3

    A gap or a missing first column

    Without country, city values are scattered. Skipping city means age isn't ordered within the slice, so it filters instead of seeking.

    Some databases can “skip scan”, but don't count on it.

  4. 4

    Equality columns first, ranges last

    Put columns tested with = before ones tested with > or BETWEEN. Then think about which prefixes other queries need.

    CREATE INDEX ON users (country, city, age)

In production

Tools and technologies

Column order in an index decides which queries it helps.

Not tied to one language — use these names when you search docs or design a system.