See Why a SQLite Query Is Slow

Use Datasette to compare SQLite query plans before and after an index.

Introduction

30 Second Summary

A filter can feel instant on one file but painfully slow on another. Without evidence, every possible fix is a guess.

In this project, you will diagnose a slow query by reading a SQLite query plan in Datasette. You will prove the fix by comparing the plan before and after adding an index.

What You'll Build

You'll run the same browser query twice, see the first plan scan the whole table, then watch the new plan use your index after the fix.

By the end of this project, you'll have:

A browser-based data view for browsing your own CSV across at least fifty thousand rows.
A before-and-after performance check that connects the slow result to a whole-table scan before confirming the indexed plan.
A saved query whose explain plan opens from its read-only page whenever you want to review the diagnosis.
An optional Secret Mission to push your skills further.

Are there any prerequisites?

You'll need Python plus either pip or uv on your computer. Step 1 handles the remaining local setup with no account or paid service.

Before We Start

Your query-plan investigation runs locally through Datasette. Datasette needs Python 3 before it can serve your data in a browser.

The pip installer adds Python packages later in the project. This step confirms that your computer can reach both tools without creating any project files.

In this step, get ready to:

Prepare a terminal for running local commands.
Confirm that Python 3 is available.
Confirm that pip is available.

Open your working tools

The terminal runs the commands that power your local project. The browser displays the Datasette interface that those commands start.

macOS

Press Command-Space bar to open Spotlight.
Type Terminal into the search field.
Press Return to open Terminal.
Press Command-Space bar again.
Type Safari into the search field.
Press Return to open Safari.

Windows

Press the Windows key to open search.
Type Windows Terminal into the search field.
Press Enter to open Windows Terminal.
Press the Windows key again.
Type Microsoft Edge into the search field.
Press Enter to open Microsoft Edge.

You should now see a terminal prompt plus an empty browser window. These two tools stay open throughout the project.

Check Python 3

A version check confirms which Python command your computer recognises. The command differs between macOS and Windows.

macOS

Read the installed Python version by running this command in Terminal:

python3 --version

What Does This Command Do?

The --version flag asks python3 to report its installed version. A result beginning with Python 3 confirms that the required runtime is available.

Windows

Read the installed Python version by running this command in Windows Terminal:

py --version

What Does This Command Do?

The py launcher finds the Python runtime on Windows. The --version flag prints the version it found.

✔️ I see Python 3

Good progress. Your terminal can reach Python 3 without any extra setup.

ⓧ Python is unavailable

Python needs to be installed before the project can continue. Use the official installer for your operating system.

macOS

Open the official Python downloads for macOS in your browser.
Select Download macOS installer for the most recent supported release.
Run the downloaded installer with its default options.
Return to Terminal.
Confirm the installation by running this command:

python3 --version

What Should I See?

You should see output beginning with Python 3. That result confirms the installer added a usable Python command.

Windows

Open the official Python downloads page in your browser.
Install the Python Install Manager using the download option for Windows.
Return to Windows Terminal.
Confirm the installation by running this command:

py --version

What Should I See?

The Python Install Manager may install the current Python release during this first run. You should then see output beginning with Python 3.

Still Cannot Reach Python?

Close your terminal after the installation. Open it again so the new command becomes available.

If the command still fails, use the official setup guide for your operating system. Help me work out why my terminal cannot find Python 3.

Verify pip

Python packages are installed through pip. Running it through Python confirms that both commands point to the same runtime.

Before you run the check, do you expect your Python installation to find pip immediately?

macOS

Ask Python to report the available pip version by running:

python3 -m pip --version

What Does This Command Do?

The -m option runs pip through the same python3 interpreter you just checked. The output identifies the available pip version plus its installation location.

Windows

Ask Python to report the available pip version by running:

py -m pip --version

What Does This Command Do?

The -m option tells the py launcher to run pip through Python. The output identifies the available pip version plus its installation location.

✔️ I see a pip version

That is the preparation complete. Python 3 can run locally. Its package installer is ready for the next step.

ⓧ pip is unavailable

Python includes a standard module that can bootstrap pip when the installer skipped it. Use the command for your operating system.

macOS

Bootstrap pip through your Python installation by running:

python3 -m ensurepip --default-pip

What Does This Command Do?

The ensurepip module installs pip when it is missing. The --default-pip option also creates the default pip command.

Confirm that pip is now available by running:

python3 -m pip --version

What Should I See?

You should see a pip version plus an installation location. This confirms that Python can now run its package installer.

Windows

Bootstrap pip through your Python installation by running:

py -m ensurepip --default-pip

What Does This Command Do?

The ensurepip module installs pip when it is missing. The --default-pip option also creates the default pip command.

Confirm that pip is now available by running:

py -m pip --version

What Should I See?

You should see a pip version plus an installation location. This confirms that Python can now run its package installer.

Still Cannot Run pip?

Check that your Python version command still works in the same terminal. A different terminal session may be pointing to another Python installation.

Use the command that matches your operating system throughout the project. Help me diagnose why pip is unavailable through Python.

Your local tools are ready without any project packages or files added yet. Next up, you can install Datasette before loading the CSV into SQLite.

Load Data into Datasette

A slow query needs enough data to expose the work happening underneath it. Your baseline also needs a filter column with no supporting index.

In this step, you load a large CSV file into a SQLite database. You then serve that database through Datasette with the datasette-explain plugin at version 0.2.2.

In this step, get ready to:

Install the local data tools with the required explain plugin.
Generate records.csv with 50,000 data rows.
Load the data into slow_queries.db and browse the records table.

Prepare the local toolkit

Datasette provides the browser interface for your database. The sqlite-utils command turns your source file into a SQLite table.

Package installation can take a minute while pip downloads the required dependencies. A quiet terminal during that time is normal.

Install the two local data tools by running these commands:

pip install datasette
pip install sqlite-utils

What do these commands add?

The first command installs the Datasette web application.
The second command installs the utility that imports your CSV into SQLite.

Confirm that both installations finish before your terminal prompt returns.

Installation not completing?

Check that your internet connection is active. Confirm that pip belongs to the Python 3 installation from earlier.

If your computer blocks a system-wide installation, use the installation method recommended by your Python setup.

Help me troubleshoot installing Datasette and sqlite-utils with pip.

The plugin version matters because release 0.2.2 supports explain plans on the stored query page you build later. First, check the plugin state.

List the plugins in your Datasette environment by running this command:

datasette plugins

What does this command show?

The output lists every installed Datasette plugin with its version. Use the result to choose the matching tab below.

✔️ I see the required plugin version

You should see datasette-explain with version 0.2.2. Your explain plugin is ready.

ⓧ I see an older plugin version

Replace the older plugin release with the required version by running these commands:

datasette install datasette-explain==0.2.2
datasette plugins

What do these commands confirm?

The first command installs the pinned plugin release into the Datasette environment. The second command confirms that 0.2.2 is active.

ⓧ The plugin is not listed

Install the required plugin release and confirm it by running these commands:

datasette install datasette-explain==0.2.2
datasette plugins

What do these commands confirm?

The installation adds the plugin to the same environment as Datasette. The follow-up list should show datasette-explain at version 0.2.2.

Plugin still missing?

Run the plugin installation from the same terminal where the datasette command works. A different Python environment can place the plugin where Datasette cannot load it.

Help me troubleshoot why Datasette cannot see the datasette-explain plugin.

Create the baseline data

A 50,000-row dataset gives SQLite enough rows to demonstrate a full-table scan. The category column includes sparse target values for the filter query in the next step.

Move to your Desktop by running this command:

cd ~/Desktop

Why start on the Desktop?

This gives the project a predictable location. You can return to its files without searching your computer.

Create the slow-query-lab folder and enter it by running these commands:

mkdir slow-query-lab
cd slow-query-lab
pwd

What should the path show?

The final command prints your current location. The path should end with Desktop/slow-query-lab.

Generate records.csv by running this command:

python3 -c "import csv

# Create 50,000 rows so scans have real work to do.
with open('records.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerow(('id', 'category'))
    # Make target rows predictable while keeping them sparse.
    for row_id in range(1, 50001):
        category = 'target' if row_id % 1000 == 0 else f'group_{row_id % 100}'
        writer.writerow((row_id, category))
"

What does this data generator do?

The header creates the id column with the category column.
The loop writes exactly 50,000 data rows beneath the header.
Every thousandth row receives the target category.

Confirm that the CSV file exists by running:

ls

What should the file list show?

You should see records.csv in the output. That file is the source data for your database.

Import the CSV into the records table by running this command:

sqlite-utils insert slow_queries.db records records.csv --csv

What does this import create?

The command creates slow_queries.db if it does not exist. It detects the CSV columns before inserting every row into records.

The import does not create an index for category. That missing index gives you the baseline needed for the later comparison.

Confirm the row count and the empty index list by running these commands:

sqlite-utils tables slow_queries.db --counts
sqlite-utils indexes slow_queries.db records --table

What should the checks show?

The table count should show 50,000 rows for records. The index check should show no index rows for that table.

Data check not matching?

If the count is lower than 50,000, remove the incomplete database file before repeating the CSV import. If an index appears for category, make sure you imported into a fresh slow_queries.db file.

Help me troubleshoot the records import or unexpected index output.

Start Datasette and inspect the table

The database is ready for its browser interface. Datasette keeps running in the terminal while you inspect the table in your browser.

Keep this terminal open while you browse the database.
Stop the server later by pressing Ctrl+C.

Before you start the server, do you expect the browser to show the original CSV file or the SQLite table created from it?

Serve slow_queries.db and open Datasette in your browser by running:

datasette slow_queries.db -o

What does this command start?

Datasette starts a local web server for slow_queries.db. The -o option opens that server in your browser.

The plugin loads with the same Datasette process. Your database remains on your computer.

Select slow_queries from the database page.
Select records from the table list.

You should see the records table with 50,000 rows. The visible columns should include id and category.

✔️ I see the records table

Nice work. Your browser is now serving the unindexed dataset that powers the query investigation.

ⓧ I'd like to double check the setup

Datasette local instance: Running locally with the datasette-explain plugin installed, serving slow_queries.db. The records table page is open in the browser.
records.csv: CSV source data containing at least 50,000 rows. It includes columns named id and category. It includes rows with category value target.
slow_queries.db: SQLite database containing table records loaded from records.csv. The records table has no index on the category column.

Browser did not open?

Keep the Datasette process running. Open the local address printed in your terminal to reach the database page.

Help me troubleshoot opening the records table in my local Datasette instance.

Your unindexed dataset is live in the browser. Next, you run a filter query against category and capture its baseline result.

Run an Unindexed Filter Query

The records table from earlier is live in Datasette. A slow filter needs a baseline before you can prove that an optimization helped.

In this step, SQLite runs a SQL query against category while no index supports that column. You will capture the matching row count plus the execution time as your unindexed baseline.

In this step, get ready to:

Enter the category filter in Datasette's SQL editor.
Run the query against the records table.
Record the row count plus execution time as your baseline.

Enter the unindexed filter query

The filter asks for every column from rows whose category value is target. Using a fixed query gives you a result that you can repeat after the database changes.

Switch back to the records table page in your browser.
Open the SQL editor for slow_queries.db from the table page.
Paste this filter into the SQL editor:

SELECT * FROM records WHERE category = 'target';

What Does This Query Do?

The SELECT * clause returns every column from each matching row.
The records name identifies the table being searched.
The WHERE category = 'target' condition keeps rows in the target category.

Check that the SQL editor contains the complete filter exactly as shown.

Query Text Look Different?

Select any existing text in the editor before pasting the filter again. Check that target uses straight single quotes.

Help me check the filter query before I run it.

Run and record the baseline

This first run becomes the measurement you compare with the indexed result later. Keep slow_queries.db unchanged so the timing represents the unindexed table.

Before you submit the query, do you expect the filter to return every row or only rows in the target category?

Submit the query using the control beneath the SQL editor.

You will see rows whose category value is target. The result area also reports the returned row count plus the execution time.

Record the returned row count here: your unindexed row count.
Record the displayed execution time here: your unindexed execution time.

Why Record Both Values?

The row count captures how much matching data the query returns. The execution time captures how long this unindexed run took.

A later comparison is meaningful when the indexed query returns the same number of rows. These two measurements give you that reference point.

No Rows or Timing Shown?

Confirm that the filter uses target in lowercase if no rows appear.
Wait for the current result to finish loading if the timing has not appeared.
Submit the query again from the SQL editor if the result area remains empty.

Help me diagnose my unindexed Datasette query result.

You now have a concrete baseline for the unindexed category filter. Next, you will inspect its query plan to see how SQLite searched the table.

Inspect the Table Scan

Your unindexed query now has a recorded row count plus execution time. Those numbers capture the slowdown you observed.

A query plan shows the access method SQLite chose. The datasette-explain plugin renders that plan in Datasette's query editor.

The plan lets you move from timing a symptom to identifying its cause. You will find the line that shows how SQLite reaches rows in records.

In this step, get ready to:

Bring the previous filter query's explain plan into view.
Identify the access line for the records table.
Connect the scan line to the unindexed baseline.

Reveal the query plan

The plugin reads the query already in the editor. It asks SQLite for a plan whenever the query text changes.

Before you inspect the plan, consider whether the unindexed filter uses a targeted lookup or examines the table broadly.

Switch back to the Datasette query page from the previous step.
Confirm that the same filter query remains in the query editor.
Scroll below the query editor until the plan is visible.

You should see the plan for the filter query beneath the editor.

What Is a Query Plan?

A query plan is a list of operations SQLite chooses to retrieve the requested rows. Each operation shows how the database reaches a table.

The plan describes the access strategy. Your recorded baseline carries the elapsed time.

Can't See the Plan?

Keep the filter query in the editor so the plugin has a query to inspect.
Refresh the existing query page if the plan area stays empty.
Confirm that the refreshed page still shows the filter query.

Ask for help with the missing plan.

Identify the full scan

Each line in the plan describes one operation. The operation that names records tells you how SQLite reaches that table.

Before you read the operation, consider which access method you expect after seeing the slower unindexed baseline.

Find the plan line that names the records table.
Read the operation at the beginning of that line.
Compare the operation with the unindexed execution time you recorded earlier.

You should see a line such as SCAN records. This line confirms a full table scan.

You found the evidence behind the slowdown. SQLite is scanning records for the unindexed filter.

What Does SCAN records Mean?

The SCAN records operation shows that SQLite evaluates rows from the records table for this filter.

The plan reports the access method. The baseline reports the elapsed time.

Seeing a Different Access Method?

Check that the plan belongs to the same category filter from the previous step.
Confirm that the browser page still serves slow_queries.db.
Confirm that the plan line names the records table.

Ask for help interpreting the unexpected access method.

Your browser now shows the full scan behind the baseline. Next, an index on category gives you a new plan to compare against this scan.

Index the Filter Column

Your earlier query plan in Datasette proved that SQLite was reading every row in records. That full scan gave you a concrete explanation for the slow filter.

An index gives SQLite a lookup path for values in the category column. This step adds that path so you can measure its effect.

In this step, get ready to:

Create a named index for the category column.
Rerun the exact filter to capture its indexed timing.
Compare the indexed result with the unindexed baseline.

Create the category index

An index stores an ordered lookup for selected column values. The category lookup lets SQLite narrow the candidate rows before reading the matching records.

Leave the terminal tab running Datasette untouched.
Open a second tab in the same terminal window.
Switch the second terminal tab to the folder containing slow_queries.db.
Create the named index by running this command:

sqlite-utils create-index slow_queries.db records category --name=idx_records_category

What does this command do?

The slow_queries.db argument identifies the database that Datasette is serving.
The records argument selects the table.
The category argument selects the indexed column.
The --name=idx_records_category option gives the index a stable name for the query plan.

Confirm the new index by running this command:

sqlite-utils indexes slow_queries.db records --table

What should you see?

You should see an index entry named idx_records_category. Its indexed column should be category.

Index missing from the output?

Check that the second terminal tab points to the folder containing slow_queries.db.
Check that the table name is records.
Keep an existing index only when the output shows idx_records_category on category.

Help me diagnose why the category index is missing.

That is the main structural change complete. Your database now has a dedicated lookup for category values.

Rerun the same filter

A fair performance comparison uses the same SQL against the same data. The returned row count should remain unchanged because the index only changes how SQLite finds those rows.

Return to the Datasette query page from earlier.
Refresh the browser page.
Set the SQL box to the exact baseline query by replacing its contents with this SQL:

SELECT * FROM records WHERE category = 'target';

Why reuse the exact query?

The WHERE category = 'target' condition matches the baseline filter.
The unchanged SQL isolates the index as the only database change.
The matching row count confirms that both runs answer the same question.

Before you rerun the query, do you expect the row count or the execution time to change?

Select Run SQL.
Enter your earlier unindexed timing here: your unindexed query time.
Record the indexed timing shown by Datasette: your indexed query time.
Record the indexed row count shown above the results: your indexed row count.
Compare the indexed row count with your unindexed baseline.

You should see the same returned row count as before. The indexed timing gives you the second measurement for your comparison.

Results do not match the baseline?

Refresh the query page if it still shows results from an earlier browser state.
Check that the SQL still filters category using the value target.
Check that records.csv was not reloaded between the two measurements.

Help me compare the two Datasette query runs.

Compare the new query plan

The datasette-explain plugin updates the plan for the SQL in the editor. The new plan reveals whether SQLite chose idx_records_category as its access path.

Before you inspect the plan, do you expect SQLite to scan the full table or search through the new index?

Scroll beneath the SQL editor to the current explain plan.
Locate the plan entry for records.
Confirm that the entry names idx_records_category.
Compare the entry with the full-scan plan you inspected earlier.

You should now see an index search on records using idx_records_category. You should no longer see a full scan of the records table.

What changed in the plan?

The earlier plan scanned every row because category had no index. The new plan searches the named index for matching category values.

Timing can vary between runs because of caching or other local activity. The plan proves that SQLite changed its access method.

Still seeing a full table scan?

Refresh the Datasette query page so the browser reloads the database schema.
Confirm that the displayed SQL filters the category column.
Rerun the index listing command to confirm that idx_records_category still exists.

Help me work out why SQLite still scans the records table.

You have replaced a full table scan with a named index search that you can prove in the browser. Next, you will save this query in the metadata file so its plan stays available from a read-only query page.

Save and Explain the Query

Your index now gives the filter a faster plan. However, the browser SQL box still depends on you pasting the statement again.

A stored query gives the test a permanent browser page. Datasette metadata keeps that query available after every reload.

In this step, get ready to:

Save a parameterized query in metadata.json.
Reload Datasette with the metadata file.
Verify the stored query plan from its read-only page.

Create the stored query metadata

The metadata.json file defines named queries outside the database. The :category parameter turns the fixed filter value into a browser input.

Switch back to the terminal running Datasette.
Stop the current server by pressing Ctrl+C.
Create metadata.json beside slow_queries.db using a plain-text editor.
Paste this content into metadata.json:

{"databases":{"slow_queries":{"queries":{"records_by_category":{"sql":"SELECT * FROM records WHERE category = :category"}}}}}

What does this metadata do?

The databases object applies the configuration to slow_queries.db.
The queries object makes records_by_category available as a named query.
The :category parameter lets the query page accept values such as target.

Save metadata.json.
Confirm metadata.json appears beside slow_queries.db in your editor's file browser.

Metadata file not saving correctly?

Check that the file name ends with .json. Make sure your editor did not add a .txt extension.

Copy the braces and quotation marks exactly. JSON rejects curly quotation marks or extra commas.

Help me check my stored query metadata.

✔️ Awesome, I've got everything!

Great. Double-check that you saved metadata.json beside the database.

ⓧ I'd like to double check the full code

{"databases":{"slow_queries":{"queries":{"records_by_category":{"sql":"SELECT * FROM records WHERE category = :category"}}}}}

Reload Datasette with metadata

Datasette reads the metadata file when the server starts. Reloading the local instance connects the stored query definition to slow_queries.db.

Reload slow_queries.db with metadata.json by running this command:

datasette slow_queries.db --metadata metadata.json

What does this command do?

The slow_queries.db argument tells Datasette which database to serve.
The --metadata option loads the stored query from metadata.json.

Return to the Datasette page from earlier.
Refresh the browser page.
Select the slow_queries database.
Confirm records_by_category appears as an available query.

Stored query not appearing?

Check that the terminal is running the command from the folder containing both slow_queries.db and metadata.json.

Stop Datasette before changing the metadata file. Start it again after saving your correction.

Help me find why my Datasette stored query is missing.

Verify the read-only explain plan

The stored page fixes the SQL while exposing category as an input. Version 0.2.2 of datasette-explain supports explain plans on this read-only stored query page.

Select the records_by_category query.
Enter target in the category field.
Predict whether the stored page will preserve the indexed plan.
Submit the query form.

You should see the rows whose category is target. The explain plan should render on the same read-only page.

In that plan, you should see SQLite using idx_records_category to search records.

What did this prove?

The 0.2.2 release added explain plans to read-only stored query pages. Your rendered plan confirms that workflow works in the local instance.

The database keeps the data and index. The metadata file keeps the reusable query definition.

Explain plan missing or scanning?

If the plan is missing, confirm that the installed plugin version is 0.2.2. Restart Datasette after correcting the installed version.

If the plan shows a full scan, confirm that idx_records_category still exists on records(category).

Help me troubleshoot the stored query explain plan.

That closes the loop. Your saved query now has a reusable parameter page with an explain plan that confirms the index is still doing the work.

Secret mission

Prove a Covering Index

Use the existing category index to test a narrower query. You will prove when an index can supply both the filter and the result without changing your database.

Clean Up Your Resources

All project resources live on your computer, with no ongoing cloud costs. Choose whether to keep your query-analysis artifacts, pause the local server, or delete everything you created.

Resources you used:

A running Datasette local process serving slow_queries.db.
The records.csv source file containing at least 50,000 data rows.
The slow_queries.db SQLite database containing the records table with the idx_records_category index.
The metadata.json file containing the records_by_category stored query.

Keep everything running

Keeping these resources preserves the before-and-after query plan for future reference. Your local server only uses computer resources while its process is running.

Keep the Datasette process running while you continue examining query plans.
Keep records.csv, slow_queries.db, and metadata.json together in their current folder.
Leave Datasette, sqlite-utils, and datasette-explain installed for later projects.
Expect no ongoing cloud charges because every resource remains local.

Pause - I'll come back to this later

Pausing shuts down the local server while preserving every project artifact. You can serve the same database again when you return.

Close the terminal window that is running Datasette.
Refresh the read-only records_by_category page in your browser.

The stored-query page no longer connects to the local server. Your database, source data, saved query, and index remain untouched.

Does the page still load?

Close any other terminal window that is running the Datasette local process.
Refresh the browser page after the process has stopped.
Ask for help if the local server keeps responding.

Delete - I don't want to use this again

Deleting these files is permanent. The removal only affects this project's local process and artifacts.

macOS

Close the terminal window that is running Datasette.
Open the folder containing records.csv in Finder.
Select records.csv, slow_queries.db, and metadata.json.
Permanently remove the selected files with Finder's deletion controls.
Return to the same folder in Finder.
Confirm that all three project files are no longer listed.

Unable to remove a file?

Confirm that every terminal window running Datasette is closed.
Ask for help if macOS still prevents deletion.

Windows

Close the terminal window that is running Datasette.
Open the folder containing records.csv in File Explorer.
Select records.csv, slow_queries.db, and metadata.json.
Permanently remove the selected files with File Explorer's deletion controls.
Return to the same folder in File Explorer.
Confirm that all three project files are no longer listed.

Unable to remove a file?

Confirm that every terminal window running Datasette is closed.
Ask for help if Windows still prevents deletion.

Nice Work!

You did it! You turned a 50,000-row CSV into a local Datasette query lab backed by SQLite. Your before-and-after query plans now prove how an index changes a lookup.

What you learned:

Loaded at least 50,000 rows from records.csv into slow_queries.db. Served the records table through Datasette for browser-based filtering.
Read the query plan for an unindexed filter. Identified the full-table scan behind the slower query.
Added idx_records_category to speed up category lookups. Confirmed the new plan used the index. Preserved records_by_category in metadata.json as a stored query with a working explain plan.
Secret Mission: Took on the optional challenge to push your query-analysis skills further.

Ready to quiz yourself?