In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE
clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.
If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.
Option 1: Use PRAGMA index_list
to Identify Partial Indexes
To determine if a table has any partial indexes, you can now use the PRAGMA index_list()
command, which includes a partial
column to indicate whether the index is partial or not.
Syntax
The syntax goes something like this:
PRAGMA schema.index_list('table_name');
This command will return a list of indexes on the specified table, along with several columns:
seq
: Sequence number of the index.name
: Name of the index.unique
: Indicates if the index is unique (1
if unique,0
if not).origin
: Shows how the index was created (c
for constraint,u
for unique, etc.).partial
: Indicates if the index is partial (1
if partial,0
if full).
If the partial
column for an index has a value of 1
, it means the index is a partial index. Otherwise, it is a full index.
Example: Checking for Partial Indexes
Let’s check for partial indexes in a table named orders
:
PRAGMA index_list('orders');
Suppose the output is as follows:
+-----+--------------------+--------+--------+---------+
| seq | name | unique | origin | partial |
+-----+--------------------+--------+--------+---------+
| 0 | idx_all_orders | 0 | c | 0 |
| 1 | idx_pending_orders | 0 | c | 1 |
+-----+--------------------+--------+--------+---------+
In this example:
idx_all_orders
is a full index becausepartial
is0
.idx_pending_orders
is a partial index becausepartial
is1
.
This output confirms that the orders
table has one partial index (idx_pending_orders
), which is defined with a condition, likely something like WHERE status = 'pending'
in its creation SQL.
Option 2: Query the sqlite_master
Table
Another way to do it is to query the sqlite_master
table. While this table doesn’t have a partial
column (to indicate whether it’s a partial index or not), it does have a sql
column, which shows us the SQL code used to create the index.
Therefore, we can include this column in our query to show us the actual CREATE INDEX
statement. If it has a WHERE
clause, then it’s a partial index.
Example:
SELECT
name,
sql
FROM sqlite_master
WHERE type = 'index'
AND tbl_name = 'orders';
Output:
+--------------------+-------------------------------------------------------+
| name | sql |
+--------------------+-------------------------------------------------------+
| idx_pending_orders | CREATE INDEX idx_pending_orders ON orders(order_date) |
| | WHERE status = 'pending' |
+--------------------+-------------------------------------------------------+
| idx_all_orders | CREATE INDEX idx_all_orders ON orders(order_date) |
+--------------------+-------------------------------------------------------+
So again, we can see that the idx_pending_orders
index is a partial index, due to the fact that it includes a WHERE
clause in its definition.
Summary
Here it is in a nutshell:
- The
PRAGMA index_list()
command includes apartial
column that contains1
for partial indexes, and0
for full indexes. - The
sqlite_master
table provides us the index’s definition, which, if it includes aWHERE
clause, tells us that it’s a partial index.