How to check if a query uses an index

Prefix the statement with EXPLAIN QUERY PLAN. A line starting SEARCH names the index the planner picked; a line starting SCAN means it reads every row. The plan is a compile-time answer: SQLite does not run the query and reports no timings, so measure separately.

Why check this

Run this whenever a query changes, a filter is added to an endpoint, or a migration touches an index. It separates a change that stays fast at production row counts from one that only looks fast against a fixture of 50 rows.

The failure it catches is an index that exists and is never used. A team adds idx_orders_user_id, the report page stays slow, and nobody reads the plan because the index is right there in the migration. On the database below, four ordinary predicates read all 600,000 rows with a matching index in place.

Prerequisites

• sqlite3 3.50 or later. The EXPLAIN QUERY PLAN reference describes each line of the tree.
• Every result on this page is SQLite. It is the only engine on the machine that verified it, so no PostgreSQL or MySQL plan appears here. EXPLAIN QUERY PLAN is not EXPLAIN ANALYZE: it prepares the statement and prints the plan without executing it, which is why the timings come from a separate script.
• Name the client, not only the engine. Plans came from the sqlite3 shell on PATH, an Android platform-tools build reporting 3.50.6 … (32-bit). Timings came from Node 22.23.2 and its bundled node:sqlite, SQLite 3.51.3. Both clients returned identical plans for the same seven statements.
• 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".
• plan-bench.mjs, which prints the plan and the time of each statement in a file. Timings come from one shared Windows laptop under other load, so read the min column.

import { DatabaseSync } from 'node:sqlite';
import { readFileSync } from 'node:fs';

const db = new DatabaseSync(process.argv[2]);
const runs = 25;

for (const sql of readFileSync(process.argv[3], 'utf8').split(';').map((s) => s.trim()).filter(Boolean)) {
  const plan = db.prepare(`EXPLAIN QUERY PLAN ${sql}`).all().map((r) => r.detail).join(' / ');
  const stmt = db.prepare(sql);
  const ms = [];
  for (let i = 0; i < runs; i += 1) {
    const t0 = process.hrtime.bigint();
    stmt.all();
    ms.push(Number(process.hrtime.bigint() - t0) / 1e6);
  }
  const sorted = ms.slice(1).sort((a, b) => a - b);
  console.log(`${plan}\n    first ${ms[0].toFixed(1)} ms   median ${sorted[Math.floor(sorted.length / 2)].toFixed(1)} ms   min ${sorted[0].toFixed(1)} ms`);
}
db.close();

Steps

1. 1

   Step 1.

   Read the plan of the query you care about before changing anything.

   sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 4242;"
   

   QUERY PLAN
   `--SCAN orders

   SCAN orders, with no index name. Every row is read and the WHERE clause applied to each. Three rows come back out of 600,000.

2. 2

   Step 2.

   Create the indexes the rest of this page uses, and confirm what now exists.

   sqlite3 shop.db "CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status); CREATE INDEX idx_orders_status_created ON orders(status, created_at); CREATE INDEX idx_orders_status_total ON orders(status, total_cents); CREATE INDEX idx_users_country_nocase ON users(country COLLATE NOCASE);" ".mode box" "SELECT m.name AS 'table', il.name AS 'index', group_concat(ii.name, ', ') AS columns FROM sqlite_schema m JOIN pragma_index_list(m.name) il JOIN pragma_index_info(il.name) ii WHERE m.type='table' GROUP BY m.name, il.name ORDER BY m.name, il.name;"
   

   ┌────────┬───────────────────────────┬─────────────────────┐
   │ table  │           index           │       columns       │
   ├────────┼───────────────────────────┼─────────────────────┤
   │ orders │ idx_orders_status         │ status              │
   │ orders │ idx_orders_status_created │ status, created_at  │
   │ orders │ idx_orders_status_total   │ status, total_cents │
   │ orders │ idx_orders_user_id        │ user_id             │
   │ users  │ idx_users_country_nocase  │ country             │
   │ users  │ sqlite_autoindex_users_1  │ email               │
   └────────┴───────────────────────────┴─────────────────────┘

   Six indexes across two tables. Every predicate below has one that matches its column.

3. 3

   Step 3.

   Read the plan of the same query again.

   sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 4242;"
   

   QUERY PLAN
   `--SEARCH orders USING INDEX idx_orders_user_id (user_id=?)

   SEARCH, the index name, then the predicate the index resolved in parentheses. Terms of the WHERE clause outside those parentheses are filtered row by row afterwards.

4. 4

   Step 4.

   Run four predicates that each have a matching index and none of them uses it.

   for q in "SELECT * FROM orders WHERE user_id + 0 = 4242" "SELECT * FROM users WHERE email LIKE 'user4242%'" "SELECT * FROM orders WHERE created_at >= '2025-01-01'" "SELECT * FROM users WHERE country = 'UA'"; do echo "$q"; sqlite3 shop.db "EXPLAIN QUERY PLAN $q;" | tail -1; done
   

   SELECT * FROM orders WHERE user_id + 0 = 4242
   `--SCAN orders
   SELECT * FROM users WHERE email LIKE 'user4242%'
   `--SCAN users
   SELECT * FROM orders WHERE created_at >= '2025-01-01'
   `--SCAN orders
   SELECT * FROM users WHERE country = 'UA'
   `--SCAN users

   Four causes. Arithmetic on the indexed column, a LIKE against a BINARY column while case_sensitive_like is off, a composite index whose leading column is absent from the predicate, and a COLLATE NOCASE index meeting a plain =. Each is repaired in the table below.

5. 5

   Step 5.

   Measure what SEARCH is worth, with the plan and the time from the same run.

   node plan-bench.mjs shop.db covering.sql
   

   SCAN orders
     first 51.6 ms   median 54.2 ms   min 41.6 ms
   SEARCH orders USING INDEX idx_orders_status (status=?)
     first 56.6 ms   median 56.1 ms   min 51.5 ms
   SEARCH orders USING COVERING INDEX idx_orders_status_total (status=?)
     first 24.1 ms   median 24.7 ms   min 23.2 ms

   covering.sql holds the same aggregate three times, with NOT INDEXED, INDEXED BY idx_orders_status and INDEXED BY idx_orders_status_total. The middle plan says SEARCH and is the slowest of the three. Adding total_cents to the index made it COVERING, the table lookup disappeared, and the minimum fell from 41.6 ms to 23.2 ms.

6. 6

   Step 6.

   Run ANALYZE between two readings of one plan, with the data unchanged.

   sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id BETWEEN 1 AND 500 AND status = 'paid';" "ANALYZE;" "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id BETWEEN 1 AND 500 AND status = 'paid';"
   

   QUERY PLAN
   `--SEARCH orders USING INDEX idx_orders_status_total (status=?)
   QUERY PLAN
   `--SEARCH orders USING INDEX idx_orders_user_id (user_id>? AND user_id<?)

   Same statement, same rows, different index. ANALYZE wrote row counts into sqlite_stat1, and with them the planner stopped preferring the equality on status.

7. 7

   Step 7.

   Time both of those plans, forced, to see what the switch was worth.

   node plan-bench.mjs shop.db flip.sql
   

   SEARCH orders USING INDEX idx_orders_status_total (status=?)
     first 128.4 ms   median 133.1 ms   min 126.5 ms
   SEARCH orders USING INDEX idx_orders_user_id (user_id>? AND user_id<?)
     first 9.0 ms   median 7.8 ms   min 7.1 ms

   flip.sql is the same statement twice, once with INDEXED BY naming each index. 126.5 ms against 7.1 ms, and nothing but the presence of sqlite_stat1 decided which one ran.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | SEARCH … USING INDEX x (col=?) | The index resolved that predicate | Read what is in the parentheses. Terms outside it are filtered row by row. | | SEARCH … USING COVERING INDEX x | Every column the query needs is in the index | Nothing. This is the cheapest shape available. | | SCAN t after arithmetic or a cast | The predicate is not on the bare column | Move the function to the literal, or index the expression itself. | | SCAN t on LIKE 'prefix%' | case_sensitive_like is off and the column is BINARY | Set PRAGMA case_sensitive_like=ON, or declare the column COLLATE NOCASE. | | SCAN t with a composite index | The leading column of the index is absent | Reorder the index, or add one led by the column you filter on. | | SCAN t on a COLLATE NOCASE index | The query compares with the default BINARY | Add COLLATE NOCASE to the comparison, as in country = 'UA' COLLATE NOCASE. | | USE TEMP B-TREE FOR ORDER BY | The sort is not served by an index | Extend the index with the sort columns in the same order. |

Common mistakes

What to check next

• How to check if a table has an index: confirm the index you expect the planner to use exists at all.
• How to check missing indexes: turns a SCAN line into a concrete CREATE INDEX proposal.
• How to find slow queries: the timing half, including work counters that do not move with machine load.
• How to check table schema: column affinity and collation, which decide half the cases above.

FAQ

What is the difference between SCAN and SEARCH?

SCAN visits every row of the table or index. SEARCH seeks to a position using a key. A SCAN of a small table is correct, and a SEARCH that matches most of a large table can be slower than a scan.

Why does the plan show my index but the query is still slow?

The index resolved the predicate and the rest of the work stayed. Read the parentheses after the index name for the part it handled, check whether a table lookup follows, and compare against NOT INDEXED with a clock.

How do I force a specific index to compare two plans?

INDEXED BY name after the table pins the choice, and NOT INDEXED forbids any index on that table. Both are for measurement. Leaving INDEXED BY in application code turns a dropped index into a query that fails to prepare.

Verified