Indexes
Speed up queries with CREATE INDEX and EXPLAIN QUERY PLAN
Skills taught: sql.create-index, sql.explain-query-plan, sql.index-strategy
Creating and using indexes
-- Create an index on email (for fast login queries)
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email_unique ON users(email);
-- Index on foreign key (speeds up JOINs)
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_product_id ON orders(product_id);
-- Composite index: ORDER BY ordered_at WHERE user_id = ?
CREATE INDEX IF NOT EXISTS idx_orders_user_date
ON orders(user_id, ordered_at);
-- See existing indexes
SELECT name, tbl_name FROM sqlite_master WHERE type = 'index';
-- Drop an index
-- DROP INDEX IF EXISTS idx_users_email;
EXPLAIN QUERY PLAN
-- Show the query plan: does it use an index?
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'alice@example.com';
-- Result with index: SEARCH users USING INDEX idx_users_email
-- Result without: SCAN users
-- Check a JOIN plan
EXPLAIN QUERY PLAN
SELECT u.name, o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.total > 1000;
-- Should show SEARCH orders USING INDEX and SEARCH users USING INDEX
-- Check sort plan
EXPLAIN QUERY PLAN
SELECT * FROM orders ORDER BY user_id, ordered_at;
-- With composite index: SCAN orders USING INDEX idx_orders_user_date
-- Without: SCAN + USE TEMP B-TREE FOR ORDER BY
Frequently Asked Questions
How do I know which indexes to create?
Start with: (1) foreign key columns — always index them for JOIN performance, (2) columns in WHERE clauses of frequent, slow queries — use EXPLAIN QUERY PLAN to identify full table scans, (3) columns in ORDER BY for pagination queries. Don't pre-create indexes speculatively — measure first, index second.