Hosting Operations

Show Indexes on a SQLite Table

You need to inspect indexes attached to one SQLite table.

Command

sqlite3 app.db "PRAGMA index_list('orders');"

What changed

Nothing changes. SQLite prints index metadata for the table.

Danger

safe

When to use it

Use before changing queries or adding indexes during performance triage.

When not to use it

Do not use it alone to prove an index is used; follow up with EXPLAIN QUERY PLAN.

Undo or recovery

No undo needed because this command is read-only.

Expected output

Index rows showing index name, uniqueness, origin, and partial-index flag.

demo script

Disposable terminal steps

sqlite3 app.db ".schema orders"
sqlite3 app.db "PRAGMA index_list('orders');"

Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel

::fixture-ready::
$ sqlite3 app.db ".schema orders"
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL, created_at TEXT NOT NULL);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, total_cents INTEGER NOT NULL, created_at TEXT NOT NULL);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE TABLE events (id INTEGER PRIMARY KEY, event_type TEXT NOT NULL, created_at TEXT NOT NULL);
::exit-code::0
$ sqlite3 app.db "PRAGMA index_list('orders');"
0|idx_orders_user_id|0|c|0
::exit-code::0

YouTube Short

List SQLite table indexes.

Use PRAGMA index_list when you need to see which indexes a table actually has.

experiments

A/B tests to run

Metric: completion_rate

A: Which indexes exist?

B: Before adding another index.