How to check missing indexes

Collect the statements your application runs, put each through EXPLAIN QUERY PLAN, and keep the ones whose plan begins SCAN. Then hand a scanning statement to the sqlite3 shell's .expert command, which proposes a CREATE INDEX for it and reprints the plan the proposal would produce.

Why check this

A missing index is found before a release, from the query list, not after one from a graph. Run this after any change that adds a filter, a sort or a join, and before the first load test, because a plan sweep takes seconds while a load test takes an afternoon and tells you less about the cause.

The failure it catches is the table nobody indexed. An orders table with a foreign key to users and no index on that column turns every "orders for this customer" call into a full read of 600,000 rows, and it passes code review, because the foreign key looks like an index and is not one.

Prerequisites

• sqlite3 3.50 or later, with the .expert command compiled in. Run .expert with no argument to confirm it is present.
• SQLite is the only engine on the machine that verified this page, so no PostgreSQL or MySQL advisor output appears here. EXPLAIN QUERY PLAN does not execute the statement and reports no timings; it answers which index would be used, not what it would cost.
• Name the client. Every output below came from the sqlite3 shell on PATH, an Android platform-tools build reporting 3.50.6 … (32-bit), except the plan sweep, which is Node 22.23.2 with its bundled node:sqlite, SQLite 3.51.3.
• The database from How to check if a table has an index, built with sqlite3 shop.db ".read shop.sql", plus app-queries.sql holding one statement per line:

SELECT * FROM orders WHERE user_id = 4242;
SELECT * FROM orders WHERE status = 'refunded' AND created_at >= '2025-01-01';
SELECT * FROM users WHERE email = 'user4242@example.com';
SELECT * FROM users WHERE country = 'UA';
SELECT * FROM orders WHERE id = 12345;

• scan-finder.mjs, which reads that file and prints the plan of every statement in it:

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

const db = new DatabaseSync(process.argv[2], { readOnly: true });
for (const q of readFileSync(process.argv[3], 'utf8').split(';').map((s) => s.trim()).filter(Boolean)) {
  const plan = db.prepare(`EXPLAIN QUERY PLAN ${q}`).all().map((r) => r.detail);
  console.log(`${plan.some((d) => d.startsWith('SCAN')) ? 'SCAN' : 'ok  '}  ${q}`);
  for (const d of plan) console.log(`        ${d}`);
}
db.close();

Steps

1. 1

   Step 1.

   Count the indexes per table, so a table with none is visible before any query runs.

   sqlite3 shop.db ".mode box" "SELECT m.name AS 'table', (SELECT count(*) FROM pragma_index_list(m.name)) AS indexes FROM sqlite_schema m WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite_%' ORDER BY indexes;"
   

   ┌────────┬─────────┐
   │ table  │ indexes │
   ├────────┼─────────┤
   │ orders │ 0       │
   │ users  │ 1       │
   └────────┴─────────┘

   orders holds 600,000 rows and carries no index. The single index on users is the one the UNIQUE constraint on email created.

2. 2

   Step 2.

   List foreign keys whose child column does not lead any index.

   sqlite3 shop.db ".mode box" "SELECT m.name AS 'child table', fk.'from' AS column, fk.'table' AS parent FROM sqlite_schema m JOIN pragma_foreign_key_list(m.name) fk WHERE m.type = 'table' AND NOT EXISTS (SELECT 1 FROM pragma_index_list(m.name) il JOIN pragma_index_info(il.name) ii WHERE ii.seqno = 0 AND ii.name = fk.'from');"
   

   ┌─────────────┬─────────┬────────┐
   │ child table │ column  │ parent │
   ├─────────────┼─────────┼────────┤
   │ orders      │ user_id │ users  │
   └─────────────┴─────────┴────────┘

   Declaring a foreign key creates no index on the child side. Every join from users to orders, and every ON DELETE check, reads the whole child table until one exists.

3. 3

   Step 3.

   Sweep the application's statements and mark the ones that scan.

   node scan-finder.mjs shop.db app-queries.sql
   

   SCAN  SELECT * FROM orders WHERE user_id = 4242
         SCAN orders
   SCAN  SELECT * FROM orders WHERE status = 'refunded' AND created_at >= '2025-01-01'
         SCAN orders
   ok    SELECT * FROM users WHERE email = 'user4242@example.com'
         SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)
   SCAN  SELECT * FROM users WHERE country = 'UA'
         SCAN users
   ok    SELECT * FROM orders WHERE id = 12345
         SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)

   Three of five scan. The two that pass use the autogenerated index on email and the rowid, neither of which anyone wrote.

4. 4

   Step 4.

   Ask the shell what index one of those statements wants.

   printf '.expert\nSELECT * FROM orders WHERE status = %s AND created_at >= %s;\n' "'refunded'" "'2025-01-01'" | sqlite3 shop.db
   

   CREATE INDEX orders_idx_a87cb64c ON orders(status, created_at);
   
   SEARCH orders USING INDEX orders_idx_a87cb64c (status=? AND created_at>?)

   The proposal first, the plan it would produce second. .expert creates the candidate in a scratch copy of the schema, reads the plan, and leaves your database untouched: no index and no sqlite_stat1 row survive the command.

5. 5

   Step 5.

   Run it against a join, where the answer covers two tables.

   printf '.expert\nSELECT o.id, o.total_cents FROM orders o JOIN users u ON u.id = o.user_id WHERE u.country = %s AND o.created_at >= %s;\n' "'UA'" "'2025-01-01'" | sqlite3 shop.db
   

   CREATE INDEX orders_idx_2623ab6b ON orders(user_id, created_at);
   CREATE INDEX users_idx_0393ea7c ON users(country);
   
   SEARCH u USING COVERING INDEX users_idx_0393ea7c (country=?)
   SEARCH o USING INDEX orders_idx_2623ab6b (user_id=? AND created_at>?)

   Two proposals and the join order they produce. The index on users(country) comes back as COVERING, because the join needs only country and the rowid from that table.

6. 6

   Step 6.

   Create the indexes under your own names, then sweep again.

   sqlite3 shop.db "CREATE INDEX idx_orders_status_created ON orders(status, created_at); CREATE INDEX idx_users_country ON users(country); CREATE INDEX idx_orders_user_id ON orders(user_id);" && node scan-finder.mjs shop.db app-queries.sql
   

   ok    SELECT * FROM orders WHERE user_id = 4242
         SEARCH orders USING INDEX idx_orders_user_id (user_id=?)
   ok    SELECT * FROM orders WHERE status = 'refunded' AND created_at >= '2025-01-01'
         SEARCH orders USING INDEX idx_orders_status_created (status=? AND created_at>?)
   ok    SELECT * FROM users WHERE email = 'user4242@example.com'
         SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)
   ok    SELECT * FROM users WHERE country = 'UA'
         SEARCH users USING INDEX idx_users_country (country=?)
   ok    SELECT * FROM orders WHERE id = 12345
         SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)

   Keep this sweep in the test suite. It is the assertion that a later migration cannot drop an index without something failing.

7. 7

   Step 7.

   Ask .expert about a statement whose column is already indexed.

   printf '.expert\nSELECT * FROM orders WHERE user_id = 4242 AND status = %s;\n' "'paid'" | sqlite3 shop.db
   

   CREATE INDEX orders_idx_e7cb1963 ON orders(user_id, status);
   
   SEARCH orders USING INDEX orders_idx_e7cb1963 (user_id=? AND status=?)

   idx_orders_user_id already exists on this database and the proposal does not mention it. Accept this one and you own two indexes where the first is a prefix of the second, both maintained on every write.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | A table with 0 indexes | Nothing can seek into it | Find out what filters it. If nothing does, leave it alone. | | A foreign key in step 2's output | The child column leads no index | Index it, unless the parent is never deleted and never joined from. | | SCAN on a small table | Reading it whole is correct | Ignore. An index on 40 rows costs writes and saves nothing. | | SCAN on a large table with a selective filter | A real missing index | Take the .expert proposal, rename it, put it in a migration. | | .expert proposes nothing | No index would change the plan | The cost is elsewhere: the sort, the join order, or the row count itself. | | A proposal that repeats a column you already index | The existing index is a prefix of the new one | Create the wider one and drop the narrower one in the same migration. |

Common mistakes

What to check next

• How to check if a table has an index: reads what exists, including the indexes no migration named.
• How to check if a query uses an index: why a plan can still say SCAN after you add the index it asked for.
• How to find slow queries: confirms the proposal was worth its write cost.
• How to check foreign key: the constraint behind the unindexed column in step 2.

FAQ

How do I find missing indexes without a query log?

Start from the schema, as in steps 1 and 2. Tables with no index and foreign keys whose child column is unindexed are findings on their own, and neither needs a single query to be captured.

Does .expert change my database?

No. It builds the candidate indexes in a scratch copy of the schema, reads the plan, and drops them. After the two runs above, the database held only the indexes created by hand, and no sqlite_stat1 table.

Why does the proposal name look like a hash?

.expert derives the name from the index definition, so the same proposal is stable across runs. It is a label for the report, not a name to ship.

Should every scanning query get an index?

No. Weigh how selective the filter is and how often the table is written. An index that matches most rows of the table can be slower than the scan, and every index adds work to every insert, update and delete.

Verified