Indexes & EXPLAIN

Code

-- Single and composite indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_published ON posts(user_id, published);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Partial index (smaller, targeted)
CREATE INDEX idx_posts_published ON posts(published)
WHERE published = 1;

-- Inspect the query plan
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 5 AND published = 1;

-- List indexes
SELECT name, sql FROM sqlite_master WHERE type = 'index';

-- Drop
DROP INDEX idx_users_email;

-- Analyze for stats (helps planner)
ANALYZE;

Explanation

SQLite uses B-tree indexes that speed up equality and range lookups, with composite indexes supporting leftmost-prefix queries. Partial indexes (WHERE clause) only index matching rows, saving space for selective predicates. EXPLAIN QUERY PLAN reveals whether the query uses an index scan or a full table scan.

More SQLite Snippets