Composite and Covering Indexes: Column Order in Database Indexing

Here is a plan that surprises people. The table has an index that includes slot_date, the query filters on slot_date alone, and the database still reads every row. Nothing is broken; the index simply is not shaped for that question. This lesson uses a clinic's appointment table to show how column order decides which queries a composite index can serve, then how to go one step further and avoid reading the table at all.

A composite index is one sorted list with tie-breakers

An index on (clinic_id, slot_date) is not two indexes. It is a single list sorted by clinic_id first, and within equal clinics by slot_date. Everything about column order follows from picturing that list.

(clinic_id, slot_date) in order (row ids made up)

 clinic  slot_date     row
   1     2025-01-03    8841
   1     2025-01-04    5519
   ...
   3     2025-06-13      77
   3     2025-06-14    9012   <- seek here, read on
   3     2025-06-14   15432
   3     2025-06-15     301
   ...
   4     2025-01-02    7260

All of clinic 3's dates sit together, in date order. That makes "clinic 3 on 14 June" one seek followed by a short read, and "clinic 3 in June" one seek followed by a slightly longer read. But the 14 June entries are scattered across the list, one cluster per clinic, so "any clinic on 14 June" has no single place to seek to.

Nine queries, nine plans

The script creates 20,000 appointments, then asks SQLite how it would run nine queries as indexes are added. EXPLAIN QUERY PLAN does not run the query; it prints the strategy. Run it yourself, since the plan text is the evidence.

import random
import sqlite3

random.seed(11)
db = sqlite3.connect(":memory:")
db.execute("""
    CREATE TABLE appointments (
        id           INTEGER PRIMARY KEY,
        clinic_id    INTEGER NOT NULL,
        doctor_id    INTEGER NOT NULL,
        slot_date    TEXT    NOT NULL,   -- 'YYYY-MM-DD'
        status       TEXT    NOT NULL,
        patient_name TEXT    NOT NULL
    )""")
db.executemany(
    "INSERT INTO appointments VALUES (?,?,?,?,?,?)",
    [(i, random.randint(1, 5), random.randint(1, 40),
      f"2025-{random.randint(1, 12):02d}-{random.randint(1, 28):02d}",
      random.choice(["booked", "booked", "booked", "done", "cancelled"]),
      f"patient{i}") for i in range(1, 20001)])
db.execute("ANALYZE")

def show(label, sql):
    print(f"-- {label}")
    for row in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("   ", row[3])

day = "WHERE clinic_id = 3 AND slot_date = '2025-06-14'"
show("1. no index", f"SELECT patient_name FROM appointments {day}")

db.execute("CREATE INDEX idx_clinic_date ON appointments (clinic_id, slot_date)")
show("2. index (clinic_id, slot_date)", f"SELECT patient_name FROM appointments {day}")
show("3. leading column skipped", "SELECT patient_name FROM appointments WHERE slot_date = '2025-06-14'")
show("4. leading column only", "SELECT patient_name FROM appointments WHERE clinic_id = 3")
show("5. equality, then range", "SELECT patient_name FROM appointments WHERE clinic_id = 3 AND slot_date >= '2025-06-01' AND slot_date < '2025-07-01'")
show("6. ORDER BY the next column", "SELECT patient_name FROM appointments WHERE clinic_id = 3 ORDER BY slot_date")
show("7. ORDER BY an unindexed column", "SELECT patient_name FROM appointments WHERE clinic_id = 3 ORDER BY doctor_id")

db.execute("CREATE INDEX idx_clinic_date_status ON appointments (clinic_id, slot_date, status)")
show("8. covering: only indexed columns", f"SELECT slot_date, status FROM appointments {day}")
show("9. one non-indexed column added", f"SELECT slot_date, patient_name FROM appointments {day}")

-- 1. no index
    SCAN appointments
-- 2. index (clinic_id, slot_date)
    SEARCH appointments USING INDEX idx_clinic_date (clinic_id=? AND slot_date=?)
-- 3. leading column skipped
    SCAN appointments
-- 4. leading column only
    SEARCH appointments USING INDEX idx_clinic_date (clinic_id=?)
-- 5. equality, then range
    SEARCH appointments USING INDEX idx_clinic_date (clinic_id=? AND slot_date>? AND slot_date<?)
-- 6. ORDER BY the next column
    SEARCH appointments USING INDEX idx_clinic_date (clinic_id=?)
-- 7. ORDER BY an unindexed column
    SEARCH appointments USING INDEX idx_clinic_date (clinic_id=?)
    USE TEMP B-TREE FOR ORDER BY
-- 8. covering: only indexed columns
    SEARCH appointments USING COVERING INDEX idx_clinic_date_status (clinic_id=? AND slot_date=?)
-- 9. one non-indexed column added
    SEARCH appointments USING INDEX idx_clinic_date_status (clinic_id=? AND slot_date=?)

Reading the results

Choosing the column order

Start from the queries you must make fast, then work down this list.

1. Put columns compared with = first. They fix a position in the sorted list.
2. Put the one range column (>, <, BETWEEN) or the ORDER BY column after the equality columns. Anything after a range column can still filter inside the index but cannot narrow the seek.
3. If two orders are both legal, prefer the one that also serves another query through its prefix. An index on (clinic_id, slot_date) also answers every clinic_id-only query, so a separate clinic_id index is redundant.
4. Do not decide by guessing which column has more distinct values. That matters when columns are interchangeable, but the shape of your queries matters more.

Covering indexes: never visiting the table

Queries 8 and 9 differ only in the output column. In 8, status is part of the index, so the engine reads the answer straight from index pages and never opens the table. SQLite labels this COVERING INDEX. PostgreSQL calls the same idea an Index Only Scan, and MySQL shows Using index in the Extra column.

One nuance: if you select only id, the query is covered even without listing id in the index. In SQLite the row id is stored in every index entry, and MySQL's InnoDB likewise stores the primary key in secondary indexes. PostgreSQL and SQL Server let you attach non-key columns with INCLUDE (column), which makes a column available for covering without affecting sort order. MySQL and SQLite have no INCLUDE; you add the column to the key instead.

Common mistakes

• Reversing the column order. Symptom: you created (slot_date, clinic_id) and the query filtering on clinic_id plus a date range reads far more index entries than expected; in PostgreSQL the EXPLAIN ANALYZE row counts on the index node are large.
• One single-column index per column instead of a composite. Symptom: the planner picks one of them and filters the rest row by row, or in PostgreSQL combines them with a bitmap step that still costs more than one well-ordered index.
• Wrapping the leading column in a function. Symptom: WHERE lower(clinic_name) = ... falls back to a scan although clinic_name is indexed; index the expression instead where your database supports it.
• Adding a covering column to a very wide key. Symptom: the index file is nearly as large as the table and inserts slow down with no read gain.

How this shows up in projects and interviews

Composite index design is the standard follow-up to this query is slow. A good answer names the query's equality columns, its range or sort column, and the columns it returns, then proposes one index and says which other queries it also serves. Interview prompts like design an index for the clinic's calendar page test exactly the leftmost-prefix reasoning above. In practice, check the existing indexes first: the best change is often extending one rather than adding another. See Advanced SQL for the query shapes that rely on these orderings, such as window functions with ORDER BY.