sqlite> CREATE TABLE FOO(A INT, B INT, C INT); sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> INSERT INTO FOO VALUES(0,0,0); sqlite> INSERT INTO FOO VALUES(1,1,1); sqlite> INSERT INTO FOO VALUES(2,2,2); sqlite> SELECT * FROM sqlite_stat1; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> INSERT INTO GOO VALUES(0,0); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> INSERT INTO GOO VALUES(1,1); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> INSERT INTO GOO VALUES(2,2); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> INSERT INTO GOO VALUES(2,2); Error: UNIQUE constraint failed: GOO.C sqlite> CREATE INDEX GOO_INDEX ON GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|GOO_INDEX|3 1 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|GOO_INDEX|4 2 GOO|sqlite_autoindex_GOO_1|4 1(Edited: 2020-03-18)
SQLite version 3.30.0 2019-10-04 15:03:17 Enter ".help" for usage hints. sqlite> create table FOO(A INT, B INT, C INT); sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> insert into FOO(A, B, C) VALUES(1, 2, 3), (4,5,6), (7,8,9); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> insert into GOO(C,D) values(1,2); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO(C,D) values(3,4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into GOO(C,D) values(5,4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into foo(A,B,C) values(1,2,3); sqlite> analyze foo; sqlite> SELECT * FROM sqlite_stat1; GOO|sqlite_autoindex_GOO_1|3 1 FOO||4 sqlite>
The second value probably keeps track of the primary key. I'm assuming that the '1' indicates that it's the first column that has the index (or that there's only one column)? I think the '3' in 'GOO|sqlite_autoindex_GOO_1|3 1' indicates the number of rows, and the '1' indicates the number of columns that is part of the index.
If we had two columns as the index, I think it would be 'GOO|sqlite_autoindex_GOO_2|3 2', but I'm not sure ¯\_(γ)_/¯
sqlite> CREATE TABLE FOO(A INT, B INT, C INT); sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, INT D); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_autoindex_GOO_1 ; Error: no such table: sqlite_autoindex_GOO_1 sqlite> .tables FOO GOO sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); Error: table GOO already exists sqlite> DROP TABLE GOO; sqlite> CREATE TABLE GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> INSERT INTO GOO VALUES (1, 2) ...> ; sqlite> INSERT INTO GOO VALUES (3, 4); sqlite> INSERT INTO GOO VALUES (5, 4); sqlite> INSERT INTO FOO VALUES (1, 2, 3); sqlite> INSERT INTO FOO VALUES (4, 5, 6); sqlite> INSERT INTO FOO VALUES (7, 8, 9); sqlite> SELECT * FROM GOO; C D
------ ---------- 1 2 3 4 5 4 sqlite> CREATE INDEX goo_index ON GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; tbl idx stat
------ ---------- ---------- GOO goo_index 3 2 GOO sqlite_aut 3 1 sqlite>
sqlite> create table FOO(A INT, B INT, C INT); sqlite> create table GOO(C INT PRIMARY KEY, D INT); sqlite> SELECT * FROM sqlite_stat1; sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; sqlite> insert into FOO (A, B, C) values (1, 2, 3); sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> insert into FOO (A, B, C) values (4, 5, 6); sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||2 sqlite> insert into FOO (A, B, C) values (7, 8, 9); sqlite> analyze FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> insert into GOO (C, D) values (1, 2); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO (C, D) values (3, 4); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into GOO (C, D) values (5, 6); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into GOO (C, D) values (7, 6); sqlite> create index D_index on GOO(D); sqlite> analyze GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|D_index|4 2 GOO|sqlite_autoindex_GOO_1|4 1 sqlite> select * from GOO; 1|2 3|4 5|6 7|6(Edited: 2020-03-18)
sqlite> create table FOO(A INT, B INT, C INT); sqlite> create table GOO(C INT PRIMARY KEY, D INT); sqlite> select * from sqlite_stat1; Error: no such table: sqlite_stat1 sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; sqlite> insert into FOO values (1, 2, 3); sqlite> select * from sqlite_stat1; sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||1 sqlite> insert into FOO values (2, 3, 4); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||2 sqlite> insert into FOO values (3, 4, 5); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; FOO||3 sqlite> insert into GOO values (1, 2); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO values (5, 2); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into GOO values (3, 3); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> CREATE INDEX ind on GOO (D); sqlite> ANALYZE GOO; sqlite> select * from sqlite_stat1; FOO||3 GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> CREATE INDEX ind2 on FOO (A); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 FOO|ind2|3 1 sqlite> CREATE INDEX ind3 on FOO (C); sqlite> ANALYZE FOO; sqlite> select * from sqlite_stat1; GOO|ind|3 2 GOO|sqlite_autoindex_GOO_1|3 1 FOO|ind3|3 1 FOO|ind2|3 1
The additional fields are for the number of rows and the avg number of rows a unique value is in.(Edited: 2020-03-18)