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.

Answer 2

Remember that an index can be comprised of more than one column of a table. So, in the case of "25112 1 1 1 1", this would be described as a composite index that is made up of 4 columns of a table. The numbers mean as follows:

• 25112 is an estimate of the total number of rows in the index
• The second integer (the first "1") is an estimate of the number of rows that have the same value in the 1st column on the index.
• The third integer (the second "1") is an estimate of the number of rows that have the same value for the first TWO columns of the index. It is NOT the "distinctness" of column 2.
• The forth integer (the third "1") is an estimate of the number of rows that have the same values for the first THREE columns on the index.
• Same logic for the last integer..

The last integer should always be one. Consider a table that has two rows and two columns with a composite index made up of column1+column2. The data is the table is:

1. Apple,Red
2. Apple,Green

The stats would look like "2 2 1". Meaning, there are 2 rows in the index. There are two rows that would be returned if only using column1 of the index (Apple and Apple). And 1 unique row that would be returned using column1+column2 (Apple+Red is unique from Apple+Green)