How to find slow queries

SQLite has no slow query log, so time the statements yourself. Turn on .timer in the sqlite3 shell for wall clock per statement, and .stats for the work counters behind it. A statement reading 599,999 rows and 1,800,014 virtual machine steps is the one to fix.

Why check this

Run this when an endpoint gets slower and the code diff explains nothing, and again before a release that changed the schema. Timing individual statements tells you which one to look at; a request trace tells you only that the request was slow.

The failure it prevents is a per-row query inside a loop. One statement at 40 ms is invisible in a test. The same statement called once per row of a 200-row report is eight seconds, and the profile of the request blames the handler rather than the database.

Prerequisites

• sqlite3 3.50 or later. The command line shell reference documents .timer, .stats and .eqp.
• SQLite is the only engine on the machine that verified this page. PostgreSQL logs slow statements through log_min_duration_statement and MySQL through slow_query_log; SQLite has neither, so nothing records a slow statement for you. No plan or number here came from those engines.
• Name the client. The .timer and .stats output came from the sqlite3 shell on PATH, an Android platform-tools build reporting 3.50.6 … (32-bit). The repeat timings came from Node 22.23.2 and its bundled node:sqlite, SQLite 3.51.3. The two clients report different CPU resolution, which step 1 shows.
• Timings are one shared Windows laptop with other work running on it, and localhost has no network in it. Treat the ordering and the ratios as the result, not the absolute milliseconds.
• The database from How to check if a table has an index, 200,000 users and 600,000 orders, built with sqlite3 shop.db ".read shop.sql".
• timer.mjs, which prepares one statement, runs it repeatedly, and reports the first run apart from the rest:

import { DatabaseSync } from 'node:sqlite';

const [file, sql, runs = '25', cache] = process.argv.slice(2);
const db = new DatabaseSync(file);
if (cache) db.exec(`PRAGMA cache_size = ${cache}`);
const stmt = db.prepare(sql);
const ms = [];
for (let i = 0; i < Number(runs); i += 1) {
  const t0 = process.hrtime.bigint();
  stmt.all();
  ms.push(Number(process.hrtime.bigint() - t0) / 1e6);
}
const rest = ms.slice(1).sort((a, b) => a - b);
console.log(`cache ${cache ?? 'default'}   first ${ms[0].toFixed(1)} ms   median ${rest[Math.floor(rest.length / 2)].toFixed(1)} ms   min ${rest[0].toFixed(1)} ms`);
db.close();

Steps

1. 1

   Step 1.

   Time one statement six times in the shell.

   printf '.timer on\n%s\n%s\n%s\n%s\n%s\n%s\n' "SELECT count(*) FROM orders WHERE user_id = 4242;" "SELECT count(*) FROM orders WHERE user_id = 4242;" "SELECT count(*) FROM orders WHERE user_id = 4242;" "SELECT count(*) FROM orders WHERE user_id = 4242;" "SELECT count(*) FROM orders WHERE user_id = 4242;" "SELECT count(*) FROM orders WHERE user_id = 4242;" | sqlite3 shop.db
   

   3
   Run Time: real 0.038 user 0.015625 sys 0.000000
   3
   Run Time: real 0.035 user 0.000000 sys 0.000000
   3
   Run Time: real 0.034 user 0.000000 sys 0.000000
   3
   Run Time: real 0.031 user 0.000000 sys 0.000000
   3
   Run Time: real 0.046 user 0.015625 sys 0.000000
   3
   Run Time: real 0.026 user 0.000000 sys 0.000000

   Three rows out of 600,000, and about 35 ms every time. Read real only. Every user and sys figure here is 0.000000 or 0.015625, the Windows clock tick, so the CPU columns cannot resolve a 35 ms statement.

2. 2

   Step 2.

   Turn on the work counters for the same statement.

   printf '.stats on\n%s\n' "SELECT count(*) FROM orders WHERE user_id = 4242;" | sqlite3 shop.db | grep -E "^3$|Page cache hits|Page cache misses|Fullscan Steps|Sort Operations|Autoindex Inserts|Virtual Machine Steps"
   

   3
   Page cache hits:                     2
   Page cache misses:                   6021
   Fullscan Steps:                      599999
   Sort Operations:                     0
   Autoindex Inserts:                   0
   Virtual Machine Steps:               1800014

   Fullscan Steps is the number of rows visited by a scan, and it is the whole table. These counters are deterministic: they do not move with machine load, so they compare across runs and across machines in a way milliseconds do not.

3. 3

   Step 3.

   Add the index and read the same counters again.

   sqlite3 shop.db "CREATE INDEX idx_orders_user_id ON orders(user_id);" && printf '.stats on\n%s\n' "SELECT count(*) FROM orders WHERE user_id = 4242;" | sqlite3 shop.db | grep -E "^3$|Page cache hits|Page cache misses|Fullscan Steps|Sort Operations|Autoindex Inserts|Virtual Machine Steps"
   

   3
   Page cache hits:                     2
   Page cache misses:                   4
   Fullscan Steps:                      0
   Sort Operations:                     0
   Autoindex Inserts:                   0
   Virtual Machine Steps:               20

   1,800,014 virtual machine steps down to 20, and 6,021 page reads down to 4. That is the size of the fix, stated without a clock.

4. 4

   Step 4.

   Time a statement that still scans, first with the default page cache and then with one large enough to hold the table.

   node timer.mjs shop.db "SELECT sum(total_cents) FROM orders WHERE status = 'paid'" 25 && node timer.mjs shop.db "SELECT sum(total_cents) FROM orders WHERE status = 'paid'" 25 -262144
   

   cache default   first 47.6 ms   median 54.4 ms   min 43.0 ms
   cache -262144   first 76.7 ms   median 37.4 ms   min 31.9 ms

   The default cache is 2 MB and this scan reads about 24 MB, so nothing is retained and every run costs about the same. Raise the cache to 256 MB and the first run pays 76.7 ms while the median of the rest falls to 37.4 ms. Same statement, same index, same rows.

5. 5

   Step 5.

   Rank the statements you care about, plan beside time.

   for q in "SELECT count(*) FROM orders WHERE user_id = 4242" "SELECT sum(total_cents) FROM orders WHERE status = 'paid'" "SELECT id, total_cents FROM orders WHERE created_at >= '2025-06-01' ORDER BY total_cents DESC LIMIT 20"; do echo "$q"; sqlite3 shop.db "EXPLAIN QUERY PLAN $q;" | tail -n +2 | sed 's/^/  /'; node timer.mjs shop.db "$q" 25 | sed 's/^/  /'; done
   

   SELECT count(*) FROM orders WHERE user_id = 4242
   `--SEARCH orders USING COVERING INDEX idx_orders_user_id (user_id=?)
   cache default   first 0.1 ms   median 0.0 ms   min 0.0 ms
   SELECT sum(total_cents) FROM orders WHERE status = 'paid'
   `--SCAN orders
   cache default   first 78.3 ms   median 49.2 ms   min 41.4 ms
   SELECT id, total_cents FROM orders WHERE created_at >= '2025-06-01' ORDER BY total_cents DESC LIMIT 20
   |--SCAN orders
   `--USE TEMP B-TREE FOR ORDER BY
   cache default   first 41.0 ms   median 42.1 ms   min 35.8 ms

   The third statement has two problems, and the second line of its plan is the one an index on created_at alone will not remove. The first statement reports 0.0 ms, which means below the resolution of this loop rather than free.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | real high, Fullscan Steps high | The statement reads the whole table | Index the filtered column, then confirm Fullscan Steps reaches 0. | | real high, Fullscan Steps 0 | Work is elsewhere | Read Sort Operations and the rest of the plan before touching indexes. | | Sort Operations above 0 | A sort runs outside any index | Extend the index with the ORDER BY columns in the same direction. | | Page cache misses high, steps low | Pages are being read, not row work | Raise cache_size, or shrink the rows the statement carries. | | First run far above the rest | The page cache was cold | Report both. Comparing a cold run with a warm one measures the cache. | | 0.0 ms in the loop | Below the timer resolution | Raise the iteration count, or read the .stats counters instead. |

Common mistakes

What to check next

• How to check if a query uses an index: reads the plan behind a slow statement, and why SEARCH is not always faster.
• How to check missing indexes: turns the SCAN lines above into concrete proposals.
• How to check if a table has an index: confirms what the table already carries before you add more.
• How to check database locks: when the statement is fast and the wait is not in the statement.

FAQ

Does SQLite have a slow query log?

No. There is no log_min_duration_statement and no slow_query_log table. Timing happens in the client: .timer in the shell, or a loop in your driver that records the duration of each prepared statement.

How many iterations should I run?

Enough that the median stops moving. Twenty-five was enough here for statements around 40 ms. For a statement near 1 ms, raise it until the reported minimum is stable across two invocations.

Should I compare the first run or the median?

Both, reported separately. The first run includes filling the page cache and the rest do not, and the gap between them is itself a result, as step 4 shows.

Why do the work counters matter if I already have milliseconds?

Fullscan Steps and Virtual Machine Steps do not change when another process takes the CPU. They give a before and after that survives a noisy machine, which milliseconds on a shared laptop do not.

Verified