How to check if a table has an index

Run PRAGMA index_list(users); in the sqlite3 shell. Every row is one index on that table, and the origin column says where it came from: c for a CREATE INDEX statement, u for a UNIQUE constraint, pk for a primary key. No rows at all means the table carries no index.

Why check this

Run this on a staging database before sign-off, and after any migration that adds a table or a constraint. A migration that forgets an index passes every functional test, because correctness does not depend on indexes. It fails in production, where the same lookup walks every row.

The failure it prevents is invisible in review. An order lookup by user_id reads 600,000 rows instead of three, and an endpoint that answered in 2 ms on a seeded test database takes 50 ms on real data.

Prerequisites

• sqlite3 3.50 or later. The PRAGMA reference documents index_list, index_info and index_xinfo.
• This page runs against SQLite only. It is the one database engine installed on the machine that verified it. The PRAGMA names below are SQLite's own and have no PostgreSQL or MySQL equivalent; those engines answer the same question through pg_indexes and SHOW INDEX.
• Name the client, not only the engine. Every output here came from the sqlite3 shell on PATH on the verifying machine, an Android platform-tools build reporting 3.50.6 … (32-bit). Node 22's node:sqlite on the same machine is SQLite 3.51.3, and the two differ in defaults: PRAGMA foreign_keys reads 0 in the shell and 1 under node:sqlite against this file. Both clients produced identical plans here.
• A database to read. Build the one used here, 200,000 users and 600,000 orders, with sqlite3 shop.db ".read shop.sql":

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  country TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  status TEXT NOT NULL,
  total_cents INTEGER NOT NULL,
  created_at TEXT NOT NULL
);
INSERT INTO users (id, email, country, created_at)
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 200000)
SELECT n, 'user' || n || '@example.com',
       CASE n % 5 WHEN 0 THEN 'UA' WHEN 1 THEN 'PL' WHEN 2 THEN 'DE'
                  WHEN 3 THEN 'US' ELSE 'FR' END,
       date('2024-01-01', '+' || (n % 700) || ' days')
FROM c;
INSERT INTO orders (id, user_id, status, total_cents, created_at)
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 600000)
SELECT n, ((n * 7919) % 200000) + 1,
       CASE n % 20 WHEN 0 THEN 'refunded' WHEN 1 THEN 'pending'
                   WHEN 2 THEN 'cancelled' ELSE 'paid' END,
       (n % 50000) + 100,
       datetime('2024-01-01', '+' || (n % 600) || ' days',
                '+' || (n % 86400) || ' seconds')
FROM c;

Steps

1. 1

   Step 1.

   Read the schema first, so you can see what it leaves out.

   sqlite3 shop.db ".schema"
   

   CREATE TABLE users (
   id INTEGER PRIMARY KEY,
   email TEXT NOT NULL UNIQUE,
   country TEXT NOT NULL,
   created_at TEXT NOT NULL
   );
   CREATE TABLE orders (
   id INTEGER PRIMARY KEY,
   user_id INTEGER NOT NULL REFERENCES users(id),
   status TEXT NOT NULL,
   total_cents INTEGER NOT NULL,
   created_at TEXT NOT NULL
   );

   There is no CREATE INDEX line in that output. One of these two tables is indexed anyway.

2. 2

   Step 2.

   Ask the users table what indexes it carries.

   sqlite3 shop.db ".mode box" "PRAGMA index_list(users);"
   

   ┌─────┬──────────────────────────┬────────┬────────┬─────────┐
   │ seq │           name           │ unique │ origin │ partial │
   ├─────┼──────────────────────────┼────────┼────────┼─────────┤
   │ 0   │ sqlite_autoindex_users_1 │ 1      │ u      │ 0       │
   └─────┴──────────────────────────┴────────┴────────┴─────────┘

   origin is u: the engine built this index to enforce the UNIQUE constraint on email. The sqlite_ name prefix marks it as autogenerated, and sqlite_schema.sql is NULL for it, which is why step 1 showed nothing.

3. 3

   Step 3.

   Count the indexes on orders instead of listing them, so an empty answer is still visible.

   sqlite3 shop.db ".mode box" "SELECT count(*) AS indexes FROM pragma_index_list('orders');"
   

   ┌─────────┐
   │ indexes │
   ├─────────┤
   │ 0       │
   └─────────┘

   Zero. The orders table has 600,000 rows, a foreign key to users, and no index. PRAGMA index_list(orders); on its own writes nothing at all here, not even a header, which is easy to read as a failed command.

4. 4

   Step 4.

   Confirm that the primary key of orders really is absent from that list rather than hidden in it.

   sqlite3 shop.db "EXPLAIN QUERY PLAN SELECT * FROM orders WHERE id = 12345;"
   

   QUERY PLAN
   `--SEARCH orders USING INTEGER PRIMARY KEY (rowid=?)

   A lookup by id is a SEARCH, not a scan, even though index_list returned nothing. An INTEGER PRIMARY KEY is the table's rowid, and the table is the B-tree keyed by it. There is no separate index to list.

5. 5

   Step 5.

   Read the columns behind an index name.

   sqlite3 shop.db ".mode box" "PRAGMA index_info(sqlite_autoindex_users_1);"
   

   ┌───────┬─────┬───────┐
   │ seqno │ cid │ name  │
   ├───────┼─────┼───────┤
   │ 0     │ 1   │ email │
   └───────┴─────┴───────┘

   seqno is the position in the index key, so the leading column is the one with seqno 0. That order decides which queries can use the index.

6. 6

   Step 6.

   List every index in the database in one statement, instead of one PRAGMA per table.

   sqlite3 shop.db ".mode box" "SELECT m.name AS 'table', il.name AS 'index', il.origin, 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.seq;"
   

   ┌───────┬──────────────────────────┬────────┬─────────┐
   │ table │          index           │ origin │ columns │
   ├───────┼──────────────────────────┼────────┼─────────┤
   │ users │ sqlite_autoindex_users_1 │ u      │ email   │
   └───────┴──────────────────────────┴────────┴─────────┘

   pragma_index_list and pragma_index_info are table-valued functions, so they join like tables. One row per index across the schema, with its columns in key order.

7. 7

   Step 7.

   Create an index, then read it with index_xinfo rather than index_info.

   sqlite3 shop.db "CREATE INDEX idx_orders_status_created ON orders(status, created_at);" ".mode box" "PRAGMA index_xinfo(idx_orders_status_created);"
   

   ┌───────┬─────┬────────────┬──────┬────────┬─────┐
   │ seqno │ cid │    name    │ desc │  coll  │ key │
   ├───────┼─────┼────────────┼──────┼────────┼─────┤
   │ 0     │ 2   │ status     │ 0    │ BINARY │ 1   │
   │ 1     │ 4   │ created_at │ 0    │ BINARY │ 1   │
   │ 2     │ -1  │            │ 0    │ BINARY │ 0   │
   └───────┴─────┴────────────┴──────┴────────┴─────┘

   Three rows for a two-column index. The third has cid of -1 and key of 0: it is the rowid, stored as payload so the engine can find the table row. coll is the collation, and it decides which comparisons the index can serve.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | origin is c | Someone wrote CREATE INDEX | The index is in your migrations and a schema diff will show it. | | origin is u | A UNIQUE constraint built it | It is real and usable, and no migration mentions it by name. | | origin is pk | A non-integer primary key built it | Same as u. An INTEGER PRIMARY KEY produces no row at all. | | partial is 1 | The index has a WHERE clause | Read sqlite_schema.sql for it. Rows outside that clause are not indexed. | | No rows returned | The table has no index | Check whether any query filters it. Start with the foreign keys. |

Common mistakes

What to check next

• How to check if a query uses an index: an index that exists is not an index the planner picks.
• How to check missing indexes: turns an empty index_list into a proposal.
• How to find slow queries: measures what the missing index costs in milliseconds.
• How to check table schema: the columns and types the index sits on.
• How to test unique constraint: the constraint that produced the autogenerated index here.

FAQ

How do I check an index in SQL without PRAGMA?

Query sqlite_schema directly: SELECT name, tbl_name, sql FROM sqlite_schema WHERE type = 'index';. Autogenerated indexes appear there with a NULL sql. Other engines expose the same list through pg_indexes or information_schema.statistics.

How do I check if a table is indexed on a specific column?

Join pragma_index_list to pragma_index_info as in step 6 and filter on the column name. Add ii.seqno = 0 to find only the indexes where that column leads, since a column in second position serves fewer queries.

Is a table without any index a defect?

Only if something filters or joins it. A table read only in full, or read by rowid, needs nothing. orders above is filtered by user_id and status, so its empty index list is a finding.

Verified