SQLite - EXPLAIN

SQLite - Analyzing Queries with EXPLAIN

To understand how SQLite executes a query, you can prefix the statement with the EXPLAIN QUERY PLAN keywords. This is an indispensable tool for debugging slow queries and optimizing database performance by analyzing the “query plan”.

While a lower-level EXPLAIN command exists (which shows virtual machine opcodes), EXPLAIN QUERY PLAN provides a high-level, human-readable description of the query execution strategy, making it far more practical for application developers.

Syntax

EXPLAIN QUERY PLAN [Your SQLite Query];

How to Interpret the Output

The output describes the steps SQLite will take. The key is to look for operations that might be inefficient on large tables:

• SCAN TABLE table_name: This indicates a full table scan. SQLite must read every single row in the table to find the ones that match your WHERE clause. This can be very slow for large tables.
• SEARCH TABLE table_name USING INDEX index_name: This is good! It means SQLite is using an index to quickly find the relevant rows without scanning the entire table.
• SEARCH TABLE table_name USING COVERING INDEX index_name: This is even better. It means the index itself contains all the columns needed for the query, so SQLite doesn’t even need to access the table data, leading to maximum performance.

Practical Example: Optimizing a Query

Let’s use the COMPANY table and analyze a query to find employees with a specific salary.

-- Sample Table
-- CREATE TABLE COMPANY(ID INT, NAME TEXT, AGE INT, SALARY REAL);

-- Analyze the query without an index
EXPLAIN QUERY PLAN SELECT NAME, AGE FROM COMPANY WHERE SALARY = 65000.0;

The initial output will show a full table scan because there is no index on the SALARY column:

-- Example Output (details may vary)
-- selectid | order | from | detail
-- ---------|-------|------|-------------------
-- 0        | 0     | 0    | SCAN TABLE COMPANY

This is inefficient. Let’s fix it by adding an index on the SALARY column and running the analysis again.

-- Step 1: Create an index to speed up salary lookups
CREATE INDEX idx_company_salary ON COMPANY(SALARY);

-- Step 2: Analyze the same query again
EXPLAIN QUERY PLAN SELECT NAME, AGE FROM COMPANY WHERE SALARY = 65000.0;

Now, the query plan shows that SQLite uses our new index, which is much faster.

-- Example Optimized Output
-- selectid | order | from | detail
-- ---------|-------|------|-----------------------------------------------------
-- 0        | 0     | 0    | SEARCH TABLE COMPANY USING INDEX idx_company_salary (SALARY=?)

Modern Feature: JSON Output

For automated analysis or integration with other tools, you can get the query plan in JSON format by adding the --json option to your query in the SQLite shell.

EXPLAIN QUERY PLAN SELECT NAME, AGE FROM COMPANY WHERE SALARY = 65000.0 --json;

This provides a structured, machine-readable version of the query plan, which is a powerful feature for advanced development and monitoring workflows.