CREATE INDEX — Single, Composite, Unique

~12 min · indexes, create-index, composite

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Three index shapes

Single-column — CREATE INDEX i ON t(col). Use for any column you filter on frequently.
Composite — CREATE INDEX i ON t(a, b). Order matters: the index helps queries filtering on a alone, or a AND b, but NOT b alone. Think of the index entries as sorted by (a,b) — you can binary-search by a, then by b within a group.
Unique — CREATE UNIQUE INDEX i ON t(col). Combines an index with a uniqueness constraint. Used implicitly by UNIQUE column declarations.

Tip: When choosing composite-index column order, put the highest-selectivity column first (the one with the most distinct values), unless your most common query filters by a less-selective leading column.

Code

Three index kinds·sql

-- Single-column
CREATE INDEX idx_msg_conv ON messages(conversation_id);

-- Composite — order matters
CREATE INDEX idx_msg_conv_created ON messages(conversation_id, created_at DESC);

-- Unique
CREATE UNIQUE INDEX idx_users_email ON users(email);

Inspecting indexes·sql

-- All indexes on a table
SELECT name, sql FROM sqlite_schema
WHERE type = 'index' AND tbl_name = 'messages';

-- Or via PRAGMA
SELECT * FROM pragma_index_list('messages');
SELECT * FROM pragma_index_info('idx_msg_conv_created');

External links

Exercise

On the messages table, create three composite indexes with different column orders: (a) (conversation_id, created_at), (b) (created_at, conversation_id), (c) (brain, conversation_id). Run three queries — by conversation_id alone, by conversation_id AND created_at, by created_at alone — and use EXPLAIN QUERY PLAN to see which index each picks. Note where the order trapped you.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousWhat Are Indexes? B-Trees Explained Next →When to Index — and When Not To

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.