SQLite EXPLAIN Statements

In SQLite, a statement may be preceded by the keyword EXPLAIN or the phrase EXPLAIN QUERY PLAN. These modifications cause the statement to behave like a query and return information about how the statement would have been executed if the EXPLAIN keyword or phrase had been left out.

The EXPLAIN keyword causes the statement to return a series of virtual machine instructions that would have been used to execute the statement if EXPLAIN had been omitted. Similarly, the EXPLAIN QUERY PLAN phrase returns the high-level query plan that would have been used to execute the statement. More information on the EXPLAIN QUERY PLAN phrase may be found here.

LiveCompare does not support EXPLAIN statements directly in the Execute SQL action’s Statements parameter. However, they may be implemented as follows.

  1. Add an Execute SQL action to a workflow and specify one or more statements in the action’s Statements parameter.
  2. Use the Define Action Parameters Wizard to add a Table Set output parameter named ‘Explanations’ to the Execute SQL action.
  3. Add an ‘Explanations’ Table Set dataset to the workflow.

An example workflow might look like this:

Execute SQL EXPLAIN example.

When the workflow is run (assuming there are no errors in the SQL statements), the Result dataset will be populated as normal, and the Explanation Table Set dataset will be populated with two tables for each executed SQL statement:

  • The EXPLAIN QUERY PLAN<n> table contains the results of running the EXPLAIN QUERY PLAN phrase against the statement.
  • The EXPLAIN<n> table contains the results of running the EXPLAIN keyword against the statement.

In each case, n represents the SQL statement number. The summary display for the Explanations Table Set dataset has the following columns:

Column Description
Name The name of the table.
NumRows The number of rows in the table.
Description The SQL statement that was evaluated, with its input workflow component names replaced with SQLite database table names.

The information returned by EXPLAIN and EXPLAIN QUERY PLAN should be used for SQL troubleshooting only, and is subject to change in newer releases of SQLite.

Execute SQL Actions