Is it possible to profile a SQLite SQL query?

Question

I know how long a query takes. I want to know how long each part of the query takes so that maybe I can cache certain parts to improve performance. The query however is decently complicated. Here is the output of explain:

[2,0,0,SCAN TABLE M]
[3,0,0,SCAN TABLE M]
[1,0,0,COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)]
[5,0,0,SCAN TABLE E]
[6,0,0,SCAN TABLE E]
[4,0,0,COMPOUND SUBQUERIES 5 AND 6 (UNION ALL)]
[8,0,0,SCAN TABLE P]
[9,0,0,SCAN TABLE P]
[7,0,0,COMPOUND SUBQUERIES 8 AND 9 (UNION ALL)]
[0,0,0,SCAN SUBQUERY 1 AS m]
[0,0,0,EXECUTE LIST SUBQUERY 10]
[0,0,0,EXECUTE LIST SUBQUERY 10]
[0,1,1,SEARCH SUBQUERY 4 AS e USING AUTOMATIC COVERING INDEX (e=?)]
[0,2,2,SCAN SUBQUERY 7 AS p]
[0,0,0,USE TEMP B-TREE FOR GROUP BY]
[0,0,0,USE TEMP B-TREE FOR ORDER BY]

Is there any way to find out how long each of those steps take?

Answer 1

You can get overall statistics for the statement's execution with sqlite3_stmt_status, but there are no finer-grained statistics.

The steps shown by EXPLAIN QUERY PLAN are not actually separate; the execution of the various operations typically is nested and interleaved.

Read Query Planning, The Query Planner, The Next-Generation Query Planner, and EXPLAIN QUERY PLAN. With that information, you usually can estimate how efficient your (sub)queries are.