SQL · · 1 min read
Before adding an index, read the plan
An SQLite exercise inspired by Harvard’s CS50 SQL session on optimizing.
Expanded from my Telegram note. The English edition is a translation and editorial adaptation.
When a query feels slow, adding an index is an easy first idea. I like the approach in Harvard’s CS50 SQL optimizing session: inspect how the database plans to find the data, then make a change you can explain.
See the access path
In SQLite, EXPLAIN QUERY PLAN shows a high-level description of the strategy. It can help you distinguish a scan from an indexed search.
The plan is not a stopwatch. Use it alongside timing on representative data. Small tables, cached pages and an unusual test dataset can make a quick experiment misleading.
Change one thing
Choose a query with a selective filter. Save its plan and execution time, create a relevant index, then run the same query again. Check both the output and the plan.
An index has a cost too: storage and additional work when data changes. More indexes are not automatically a better design.
Practice with a reason
The In a Snap assignment is a practical follow-up to the lecture. For every proposed index, write down which query it is intended to help and why its column order makes sense.
This note uses SQLite syntax. Other database systems expose plans differently, so use the equivalent command for your system rather than copying the command without checking.