TopolearnOne well-designed composite index often replaces several single-column ones and serves a hot query alone.
A composite index on (a, b, c) sorts rows by a, then by b within equal a, then by c. Like a phone book sorted by last name then first name, it helps queries that constrain a leftmost prefix of the columns:
WHERE a = 1andWHERE a = 1 AND b = 2use it well.WHERE b = 2alone generally can't use it efficiently (some engines can skip-scan, but don't count on it).WHERE a = 1 ORDER BY breads rows already sorted — no sort step.
Rules of thumb for column order: put columns compared with equality first, then the column used for a range or ORDER BY last. Once a range appears (b > 5), columns after it can't narrow the search further.
A covering index contains every column the query needs, so the database answers from the index without visiting the table (an index-only scan). PostgreSQL and SQL Server let you add non-key payload columns with INCLUDE; in MySQL InnoDB every secondary index implicitly contains the primary key.
Composite indexes also make some single-column indexes redundant: (user_id, created_at) already serves WHERE user_id = ?. Before adding an index, check whether an existing one with the same leading column covers it.
Example
CREATE TABLE message (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
chat_id int NOT NULL, sent_at timestamptz NOT NULL,
body text NOT NULL);
INSERT INTO message (chat_id, sent_at, body)
SELECT g % 1000, now() - g * interval '1 second', 'hi'
FROM generate_series(1, 200000) g;
-- equality column first, then the sort column; INCLUDE makes it covering
CREATE INDEX message_chat_sent_idx ON message (chat_id, sent_at DESC) INCLUDE (body);
VACUUM ANALYZE message;
EXPLAIN SELECT sent_at, body FROM message
WHERE chat_id = 7 ORDER BY sent_at DESC LIMIT 20;
-- Limit -> Index Only Scan using message_chat_sent_idx on messageRemember
- Composite indexes serve queries on a leftmost prefix of their columns
- Equality columns first, then the range or ORDER BY column
- Covering indexes let the database skip the table entirely
Watch out for
- Creating (created_at, user_id) for a query that filters user_id = ? and sorts by created_at.