303 — Indexes and Query Performance

Advanced

Learn how database indexes work, when to create them, and how to use SQLite's EXPLAIN QUERY PLAN to measure their impact. Optimize real queries step by step and understand the tradeoffs of indexing without over-trusting any single plan output.

Learning Objectives

Understand what an index is and how it speeds up queries

Create single-column and composite indexes

Read EXPLAIN QUERY PLAN output to identify slow queries

Understand column order in composite indexes (leftmost prefix rule)

Know when NOT to index and the cost of over-indexing

Optimize a slow query step by step using measurement

Treat query plans as evidence and hints, not as rigid promises

Step 1

What is an index?

An index is like the index in the back of a textbook. Without it, the database scans every row (reads the whole book). With it, the database jumps straight to the matching rows (looks up the page number). See this in action.

Commands to Run

sqlite3 ecommerce.db "SELECT COUNT(*) FROM orders;"

sqlite3 ecommerce.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 5;"

sqlite3 ecommerce.db ".indexes orders"

What This Does

EXPLAIN QUERY PLAN shows how SQLite plans to execute a query without actually running it. The output tells you whether the database will SCAN (read every row) or SEARCH (use an index to jump directly). Our schema already created some indexes, and `.indexes` shows them. The index idx_orders_customer on customer_id means looking up orders by customer is fast. Without that index, SQLite would have to read every order to find the ones belonging to customer 5.

Expected Outcome

First command shows the total number of orders. EXPLAIN QUERY PLAN shows 'SEARCH orders USING INDEX idx_orders_customer' — it's using the index, not scanning. The .indexes command lists all existing indexes on the orders table.

Pro Tips

1
EXPLAIN QUERY PLAN is your most important performance tool — use it before and after adding indexes
2
SCAN = slow (reads all rows), SEARCH = fast (uses index)
3
SQLite automatically creates indexes on PRIMARY KEY and UNIQUE columns

Common Mistakes to Avoid

⚠️Never guessing about performance — always measure with EXPLAIN QUERY PLAN
⚠️Confusing EXPLAIN with EXPLAIN QUERY PLAN — the latter gives a higher-level, more readable output in SQLite

Was this step helpful?