Covering Indexes

~12 min · indexes, covering, performance

Level 0Scout

0 XP0/80 lessons0/10 achievements

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

The query never touches the table

A covering index includes every column the query needs — both in the WHERE clause and in the SELECT list. SQLite can satisfy the query entirely from the index B-tree without touching the main table at all. EXPLAIN QUERY PLAN reports this as USING COVERING INDEX.

Two ways to make an index 'cover' a query:

Add the SELECTed columns to the composite index after the filtering ones.
Use SQLite's index expressions to project derived values you frequently SELECT.

Warning: Wider indexes cost more in write overhead and disk space. Covering is a precision tool, not a default. Apply it to known-hot queries, not every query you write.

Code

Plain index vs covering index·sql

-- Plain — index helps WHERE, but SELECT must read the table
CREATE INDEX idx_msg_conv ON messages(conversation_id);

EXPLAIN QUERY PLAN
SELECT id, role, created_at FROM messages WHERE conversation_id = 1;
-- 0|0|0|SEARCH messages USING INDEX idx_msg_conv (conversation_id=?)

-- Covering — index has every column the query needs
CREATE INDEX idx_msg_conv_cover
  ON messages(conversation_id, role, created_at, id);

EXPLAIN QUERY PLAN
SELECT id, role, created_at FROM messages WHERE conversation_id = 1;
-- 0|0|0|SEARCH messages USING COVERING INDEX idx_msg_conv_cover ...

External links

Covering Index in the Query Planner

Exercise

Take a query that EXPLAIN says is doing SEARCH ... USING INDEX. Add a covering index that includes all SELECTed columns. Confirm EXPLAIN switches to USING COVERING INDEX. Time the before-and-after to see if it actually mattered for the row counts and sizes you're working with.

Progress

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

← PreviousEXPLAIN QUERY PLAN — Read the Truth Next →Partial and Expression Indexes

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

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.