Back to journal

SQL · · 1 min read

Before adding an index, read the plan

An SQLite exercise inspired by Harvard’s CS50 SQL session on optimizing.

Before adding an index, read the plan

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.

Ali Rashedi