Once you know which indexes exist on a table — which is what the index_list PRAGMA tells you — the next natural question is: what columns does each of those indexes actually cover, and in what order? That’s precisely what PRAGMA index_info answers. I want to walk you through this PRAGMA in detail, explain exactly how to read its output, and show you the practical situations where I reach for it in real database work.
What Is index_info?
PRAGMA index_info(index_name); returns detailed information about the columns that make up a specific index, identified by name. While index_list gives you a table-level overview of which indexes exist, index_info zooms in on a single index and tells you exactly which columns it’s built from and in what order they appear within the index itself.
This distinction matters enormously for a very practical reason: the order of columns within a multi-column (composite) index directly affects which queries that index can actually help with. Understanding index_info is essential if you want to reason correctly about whether a given index will actually be used by a particular query.
Basic Syntax
PRAGMA index_info(index_name);
Just like other informational PRAGMAs, you can also write it with quotes:
PRAGMA index_info('index_name');
A Practical Example
Let’s create a table and a composite index to work with:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date TEXT,
status TEXT
);
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
Now let’s inspect this index:
PRAGMA index_info(idx_orders_customer_status);
This returns something like:
| seqno | cid | name |
|---|---|---|
| 0 | 1 | customer_id |
| 1 | 3 | status |
Let’s break down exactly what each column means.
Understanding the Output Columns
seqno
This is the position of the column within the index itself, starting from 0. In our example, customer_id has seqno = 0, meaning it’s the first (leftmost) column in the index, and status has seqno = 1, meaning it’s the second column.
This ordering is critically important for how SQLite’s query planner can use the index. SQLite can efficiently use a composite index for lookups on its leading (leftmost) column alone, or on the leading column plus additional columns in sequence, but it generally cannot efficiently use a composite index to search based only on a non-leading column in isolation. In our example, a query filtering only on status (without also filtering on customer_id) generally won’t benefit much from idx_orders_customer_status, but a query filtering on customer_id alone, or on both customer_id and status together, can make good use of it.
cid
This stands for “column ID,” and it refers to the column’s position within the original table definition (not within the index). This is the same numbering you’d see if you ran PRAGMA table_info(orders); — column IDs start at 0 for the first column defined in the table.
In our orders table, the columns in definition order are: id (cid 0), customer_id (cid 1), order_date (cid 2), status (cid 3). This matches what we saw in the output — customer_id has cid = 1 and status has cid = 3, exactly matching their positions in the original CREATE TABLE statement, regardless of their order within the index.
There’s a special case worth knowing: a cid value of -1 indicates the table’s rowid, which can appear as an implicit part of certain indexes.
name
This is simply the column name, given directly for convenience so you don’t have to cross-reference the cid against a separate table_info query if all you need is the readable name.
Why Column Order Within an Index Matters So Much
I want to spend a bit more time on this because it’s the single most important practical lesson index_info teaches you. Consider our composite index on (customer_id, status). SQLite builds this index as a sorted structure where rows are first sorted by customer_id, and within each customer_id group, further sorted by status. This is conceptually similar to how a phone book is sorted by last name first, then by first name within each last name group.
This means:
-- Efficient: uses the index fully, filtering on both columns
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';
-- Efficient: uses the index for the leading column
SELECT * FROM orders WHERE customer_id = 42;
-- Generally NOT efficient via this particular index: status alone isn't the leading column
SELECT * FROM orders WHERE status = 'shipped';
That last query might still run correctly, of course — SQLite will simply fall back to scanning the whole table (or using a different, more suitable index if one exists) rather than efficiently using idx_orders_customer_status. If filtering by status alone is a common, important query in your application, you’d want a separate index specifically on status, or an index ordered as (status, customer_id) instead.
This is exactly the kind of insight index_info gives you clarity on — by seeing the literal column order stored within an index, you can reason precisely about which query patterns that index will actually help.
Descending Columns in an Index
SQLite also lets you specify ASC or DESC directly when creating an index, which affects how it’s physically stored and can help certain sort operations avoid a separate sorting step.
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);
Interestingly, PRAGMA index_info itself doesn’t directly expose the ascending/descending sort direction of each column — for that level of detail, you’d need to look at the PRAGMA index_xinfo variant (an extended version of index_info) or inspect the original CREATE INDEX SQL text via sqlite_master. I mention this so you know the boundary of what index_info covers versus its close relative.
index_info vs. index_xinfo
It’s worth briefly clarifying the difference between index_info and the related index_xinfo PRAGMA, since they’re easy to confuse.
PRAGMA index_info(index_name); shows only the columns that are explicitly part of the key you defined when creating the index.
PRAGMA index_xinfo(index_name); shows an extended view, additionally including any columns SQLite implicitly appends to make the index fully unique for internal purposes (such as the table’s rowid), along with extra metadata columns like desc (sort direction) and coll (collation sequence used for comparisons).
PRAGMA index_xinfo(idx_orders_customer_status);
Returns something like:
| seqno | cid | name | desc | coll | key |
|---|---|---|---|---|---|
| 0 | 1 | customer_id | 0 | BINARY | 1 |
| 1 | 3 | status | 0 | BINARY | 1 |
| 2 | -1 | (rowid) | 0 | BINARY | 0 |
Notice the extra row with cid = -1, representing the implicit rowid column that SQLite appends to the end of every index to guarantee each index entry maps back uniquely to a specific row, along with the key column, which tells you whether each row is genuinely part of the defined index key (1) or an auxiliary column added by SQLite (0).
For most everyday purposes, index_info gives you exactly what you need — the defined columns, their order, and their names. Reach for index_xinfo when you specifically need sort direction, collation details, or the full internal structure including the implicit rowid column.
Using index_info Together With index_list
In practice, I almost always use these two PRAGMAs together, in a two-step workflow:
-- Step 1: find out what indexes exist on a table
PRAGMA index_list(orders);
-- Step 2: for each index name returned, inspect its actual columns
PRAGMA index_info(idx_orders_customer_status);
This combination gives you a complete picture: which indexes exist, and exactly what each one covers, without ever needing to open a schema file or dig through old migration scripts.
Practical Use Case: Diagnosing Why a Query Isn’t Using an Index
Let’s say I have a slow query:
SELECT * FROM orders WHERE status = 'shipped' ORDER BY order_date;
And I know there’s an index on the table, but the query still seems to be scanning every row. My first move is to check index_list(orders) to see what indexes exist, then run index_info on each one to check their column order. If I find that the only relevant index is idx_orders_customer_status, ordered as (customer_id, status), I immediately understand why this particular query isn’t benefiting from it — the query filters on status, which is the second (non-leading) column in that index, not the first.
Armed with that understanding, I know exactly what corrective action to take: create a new index specifically ordered to match this query’s actual filtering pattern, something like:
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
Practical Use Case: Verifying a Composite Index After Migration
After running a migration that’s supposed to add a specific composite index in a specific column order, I like to directly verify the result rather than assume the migration script did exactly what I intended:
PRAGMA index_info(idx_new_composite_index);
If the seqno ordering doesn’t match what I expected — say, the columns came out reversed from what I intended — I know immediately that something in the migration script needs correcting, well before this mistake causes confusing performance issues down the line.
Common Pitfalls
Assuming index_info shows sort direction. It doesn’t — use index_xinfo if you need to know whether a column is indexed ascending or descending.
Confusing cid with seqno. Remember, cid reflects the column’s position in the table, while seqno reflects its position within the index. These are frequently different numbers for the same column, and mixing them up leads to incorrect conclusions about index structure.
Querying an index name that doesn’t exist. Like index_list, this simply returns an empty result set rather than an explicit error — always double-check your spelling of the index name if you get back nothing unexpectedly.
Forgetting that column order determines usefulness. The most common mistake I see is creating a composite index without carefully thinking through which column should lead. index_info is your tool for verifying, after the fact, that the order actually matches your intended query patterns.
Best Practices
- Always check
index_infobefore assuming an existing index will help a specific query — column order matters enormously. - Use
index_listandindex_infotogether as your standard two-step process for full index inspection. - Reach for
index_xinfowhen you specifically need sort direction or collation details. - Design composite indexes with the leading column matching your most common, most selective filter condition.
- Verify migrations that create composite indexes by directly inspecting
index_infooutput rather than just trusting that the script ran without error. - Document the intended purpose of each composite index, including which query patterns it’s designed to serve — this makes future
index_infochecks much faster to interpret.
Covering Indexes and index_info
One particularly valuable use of index_info is identifying whether a given index is a “covering index” for a specific query — meaning the index alone contains every column the query needs, letting SQLite satisfy the entire query directly from the index without ever touching the underlying table data. This is one of the fastest possible ways for SQLite to execute a query, since it avoids the extra step of looking up the full row after finding a match in the index.
CREATE INDEX idx_orders_covering ON orders(customer_id, status, order_date);
PRAGMA index_info(idx_orders_covering);
Returns:
| seqno | cid | name |
|---|---|---|
| 0 | 1 | customer_id |
| 1 | 3 | status |
| 2 | 2 | order_date |
If a query only needs customer_id, status, and order_date — nothing else from the orders table — this index can fully cover it:
SELECT status, order_date FROM orders WHERE customer_id = 42;
Since every column referenced in this query (customer_id, status, order_date) appears within the index itself, SQLite can answer the entire query using only the index structure, never touching the main table storage. You can confirm this behavior directly using EXPLAIN QUERY PLAN, which will typically report something like “USING COVERING INDEX” when this optimization applies. Understanding exactly which columns an index covers, via index_info, is the first step toward deliberately designing covering indexes for your most performance-critical queries.
index_info on Automatically Created Indexes
index_info works identically whether the index was created explicitly with CREATE INDEX or automatically by SQLite to enforce a UNIQUE or PRIMARY KEY constraint. This is useful for confirming exactly what a constraint-backed index actually covers, especially for composite unique constraints.
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
semester TEXT,
UNIQUE (student_id, course_id, semester)
);
Running index_list(enrollments) would show an auto-generated index (something like sqlite_autoindex_enrollments_1), and following up with index_info on that name confirms the exact three-column composite structure enforcing the uniqueness rule, in the exact order specified in the original UNIQUE clause.
Practical Use Case: Choosing Between Multiple Candidate Indexes
When a table has several indexes that could seemingly apply to a given query, index_info helps you reason about which one SQLite is most likely to actually choose, and whether you’ve built the right one in the first place. Suppose orders has two indexes:
CREATE INDEX idx_a ON orders(customer_id, order_date);
CREATE INDEX idx_b ON orders(order_date, customer_id);
Checking index_info on each confirms the column order difference, which matters enormously depending on your query pattern. A query filtering by a specific customer_id across a range of dates benefits from idx_a (customer first, since it’s likely the more selective, equality-filtered column), while a query scanning a specific date range across all customers benefits from idx_b (date first). Without inspecting index_info, it’s easy to create redundant or misaligned indexes without realizing the subtle but important difference between them.
Frequently Asked Questions
Does index_info tell me how many rows match a query?
No — index_info describes structure only, not row counts or selectivity statistics. For that kind of information, you’d look at SQLite’s internal statistics tables (populated by ANALYZE), specifically sqlite_stat1.
Can index_info show me expression-based indexes?
Yes, though the name column for an expression-based index column (an index built on a computed expression rather than a plain column reference) will typically show as NULL rather than a real column name, since there’s no single underlying column being indexed directly in that position.
Is index_info available for indexes on views?
No — SQLite does not support creating indexes directly on views. Indexes can only be created on actual tables; views are computed dynamically from their underlying query each time they’re referenced.
How is index_info different from just reading the CREATE INDEX statement in sqlite_master?
Functionally, for indexes created explicitly via CREATE INDEX, both approaches can tell you the same underlying information, but index_info gives it to you in a clean, structured, already-parsed format — no need to parse SQL text yourself. This becomes essential for automatically generated indexes (from UNIQUE or PRIMARY KEY constraints), which don’t have a straightforward CREATE INDEX statement recorded in sqlite_master at all.
Wrapping Up
PRAGMA index_info is the precise, column-level companion to the broader, table-level view that index_list gives you. Where index_list tells you an index exists, index_info tells you exactly what it’s made of — which columns, in what order — and that ordering is the single biggest factor determining whether a given index will actually help a particular query. Once you get comfortable reading index_info output, diagnosing “why isn’t my index being used” problems becomes a fast, methodical process instead of a guessing game.