Explanation of sqlite_stat1 table

Question

I'm trying to diagnose why a particular query is slow against SQLite. There seems to be plenty of information on how the query optimizer works, but scant information on how to actually diagnose issues.

In particular, when I analyze the database I get the expected sqlite_stat1 table, but I don't know what the stat column is telling me. An example row is:

MyTable,ix_id,25112 1 1 1 1

What does the "25112 1 1 1 1" actually mean?

As a wider question, does anyone have any good resources on the best tools and techniques for diagnosing SQLite query performance?

Thanks

Answer 1

from analyze.c:

/* Store the results.  
**
** The result is a single row of the sqlite_stmt1 table.  The first
** two columns are the names of the table and index.  The third column
** is a string composed of a list of integer statistics about the
** index.  The first integer in the list is the total number of entires
** in the index.  There is one additional integer in the list for each
** column of the table.  This additional integer is a guess of how many
** rows of the table the index will select.  If D is the count of distinct
** values and K is the total number of rows, then the integer is computed
** as:
**
**        I = (K+D-1)/D
**
** If K==0 then no entry is made into the sqlite_stat1 table.  
** If K>0 then it is always the case the D>0 so division by zero
** is never possible.