Yioop - PHP Search Engine

-- Mar 18 In-Class Exercise

 create table FOO(A INT, B INT, C INT);
 create table GOO(C INT PRIMARY KEY, D INT);

 SELECT * FROM sqlite_stat1;
 No such table

 ANALYZE FOO;
 Nothing

 insert into FOO(A, B, C) values (10,11,12);
 ANALYZE FOO;
 Nothing 
 SELECT * FROM sqlite_stat1;
 FOO||1

 insert into FOO(A, B, C) values (20,21,22);
 ANALYZE FOO;
 Nothing 
 SELECT * FROM sqlite_stat1;
 FOO||2
 
 insert into FOO(A, B, C) values (30,31,32);
 ANALYZE FOO;
 Nothing 
 SELECT * FROM sqlite_stat1;
 FOO||3
 
  insert into GOO(C, D) values (1, 2);
 sqlite>ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|1 1
 
 sqlite>  insert into GOO(C, D) values (2, 4);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|2 1

 insert into GOO(C, D) values (3, 4);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|3 1
 sqlite> create index D_index on GOO(D);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|D_index|3 2
 GOO|sqlite_autoindex_GOO_1|3 1

(Edited: 2020-03-18)

create table FOO(A INT, B INT, C INT); create table GOO(C INT PRIMARY KEY, D INT); SELECT * FROM sqlite_stat1; No such table ANALYZE FOO; Nothing insert into FOO(A, B, C) values (10,11,12); ANALYZE FOO; Nothing SELECT * FROM sqlite_stat1; FOO||1 insert into FOO(A, B, C) values (20,21,22); ANALYZE FOO; Nothing SELECT * FROM sqlite_stat1; FOO||2 insert into FOO(A, B, C) values (30,31,32); ANALYZE FOO; Nothing SELECT * FROM sqlite_stat1; FOO||3 insert into GOO(C, D) values (1, 2); sqlite>ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into GOO(C, D) values (2, 4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 insert into GOO(C, D) values (3, 4); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> create index D_index on GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|D_index|3 2 GOO|sqlite_autoindex_GOO_1|3 1

-- Mar 18 In-Class Exercise

 Microsoft Windows [Version 10.0.18362.720]
 (c) 2019 Microsoft Corporation. All rights reserved.
 
 C:\Users\djgon>sqlite3 foo.db
 SQLite version 3.31.1 2020-01-27 19:55:54
 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> ANALYZE GOO;
 sqlite> INSERT INTO FOO VALUES (0,0,0);
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||1
 sqlite> INSERT INTO FOO VALUES (1,1,1);
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||2
 sqlite> INSERT INTO FOO VALUES (2,2,2);
 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,1);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|sqlite_autoindex_GOO_1|3 1
 sqlite> CREATE INDEX index_d ON GOO(D);
 sqlite> ANALYZE GOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 GOO|index_d|3 2
 GOO|sqlite_autoindex_GOO_1|3 1

After running the first SELECT FROM sqlite_stat1, we can see there are no statistics available. The second value in the stat column of sqlite_stat1 tracks the average occurrence of the first two columns/attributes.

(Edited: 2020-03-18)

Microsoft Windows [Version 10.0.18362.720] (c) 2019 Microsoft Corporation. All rights reserved. C:\Users\djgon>sqlite3 foo.db SQLite version 3.31.1 2020-01-27 19:55:54 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> ANALYZE GOO; sqlite> INSERT INTO FOO VALUES (0,0,0); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> INSERT INTO FOO VALUES (1,1,1); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||2 sqlite> INSERT INTO FOO VALUES (2,2,2); 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,1); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> CREATE INDEX index_d ON GOO(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|index_d|3 2 GOO|sqlite_autoindex_GOO_1|3 1 After running the first SELECT FROM sqlite_stat1, we can see there are no statistics available. The second value in the stat column of sqlite_stat1 tracks the average occurrence of the first two columns/attributes.

-- Mar 18 In-Class Exercise

$ sqlite3 foo.db
SQLite version 3.28.0 2019-04-15 14:49:49
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 (6,0,0);
sqlite> ANALYZE FOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||1
sqlite> insert into foo(A,B,C) values (5,1,1);
sqlite> ANALYZE FOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||2
sqlite> insert into foo(A,B,C) values (4,2,2);
sqlite> ANALYZE FOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
sqlite> insert into goo(C,D) values (0,9);
sqlite> ANALYZE GOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
GOO|sqlite_autoindex_GOO_1|1 1
sqlite> insert into goo(C,D) values (4,5);
sqlite> ANALYZE GOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
GOO|sqlite_autoindex_GOO_1|2 1
sqlite> insert into goo(C,D) values (8,1);
sqlite> ANALYZE GOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
GOO|sqlite_autoindex_GOO_1|3 1
sqlite> insert into goo(C,D) values (2,1);
sqlite> ANALYZE GOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
GOO|sqlite_autoindex_GOO_1|4 1
sqlite> create index goo_i on goo(D);
sqlite> ANALYZE GOO;
sqlite> SELECT * FROM sqlite_stat1;
FOO||3
GOO|goo_i|4 2
GOO|sqlite_autoindex_GOO_1|4 1
sqlite>

The second value in the stat column of sqlite_stat1 keeps track of the number of rows within a table. The additional fields in the stat column of sqlite_stat1 are for the average number of rows with the same index as their value.(Edited: 2020-03-18)

<pre> $ sqlite3 foo.db SQLite version 3.28.0 2019-04-15 14:49:49 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 (6,0,0); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||1 sqlite> insert into foo(A,B,C) values (5,1,1); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||2 sqlite> insert into foo(A,B,C) values (4,2,2); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> insert into goo(C,D) values (0,9); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|1 1 sqlite> insert into goo(C,D) values (4,5); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|2 1 sqlite> insert into goo(C,D) values (8,1); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|3 1 sqlite> insert into goo(C,D) values (2,1); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|sqlite_autoindex_GOO_1|4 1 sqlite> create index goo_i on goo(D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 GOO|goo_i|4 2 GOO|sqlite_autoindex_GOO_1|4 1 sqlite> </pre> The second value in the stat column of sqlite_stat1 keeps track of the number of rows within a table. The additional fields in the stat column of sqlite_stat1 are for the average number of rows with the same index as their value.

-- Mar 18 In-Class Exercise

CREATE TABLE FOO (A INTEGER, B INTEGER, C INTEGER); CREATE TABLE GOO(C INTEGER PRIMARY KEY, D INTEGER)

After run ANALYZE FOO, then SELECT * FROM sqlite_stat1, nothing returns After run ANALYZE GOO, then SELECT * FROM sqlite_stat1, nothing returns.

sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1; sqlite> INSERT INTO FOO (A, B, C) VALUES (1, 2, 3); sqlite> INSERT INTO FOO (A, B, C) VALUES (4, 5, 6); sqlite> INSERT INTO FOO (A, B, C) VALUES (6, 7, 8); sqlite> INSERT INTO GOO (C, D) VALUES (3, 4); sqlite> INSERT INTO GOO (C, D) VALUES (4, 4); sqlite> INSERT INTO GOO (C, D) VALUES (5, 10);

sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; GOO||3 sqlite> CREATE INDEX idx_D ON GOO (D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; GOO|idx_D|3 2

CREATE TABLE FOO (A INTEGER, B INTEGER, C INTEGER); CREATE TABLE GOO(C INTEGER PRIMARY KEY, D INTEGER) After run ANALYZE FOO, then SELECT * FROM sqlite_stat1, nothing returns After run ANALYZE GOO, then SELECT * FROM sqlite_stat1, nothing returns. sqlite> SELECT * FROM sqlite_stat1; Error: no such table: sqlite_stat1; sqlite> INSERT INTO FOO (A, B, C) VALUES (1, 2, 3); sqlite> INSERT INTO FOO (A, B, C) VALUES (4, 5, 6); sqlite> INSERT INTO FOO (A, B, C) VALUES (6, 7, 8); sqlite> INSERT INTO GOO (C, D) VALUES (3, 4); sqlite> INSERT INTO GOO (C, D) VALUES (4, 4); sqlite> INSERT INTO GOO (C, D) VALUES (5, 10); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; GOO||3 sqlite> CREATE INDEX idx_D ON GOO (D); sqlite> ANALYZE GOO; sqlite> SELECT * FROM sqlite_stat1; GOO|idx_D|3 2

-- Mar 18 In-Class Exercise

Resource Description for Screen Shot 2020-03-18 at 5.13.49 PM.png

The first number is how many index rows we have.
The second number is the avg number of duplicate values.

(Edited: 2020-03-18)

((resource:Screen Shot 2020-03-18 at 5.13.49 PM.png|Resource Description for Screen Shot 2020-03-18 at 5.13.49 PM.png)) * The first number is how many index rows we have. * The second number is the avg number of duplicate values.

-- Mar 18 In-Class Exercise

create table foo(a int, b int, c int);

create table goo(c int primary key, d int);

select * from sqlite_stat1; // Prints out error: “no such table: sqlite_stat1”

analyze foo;

select * from sqlite_stat1; // Nothing prints

analyze goo;

select * from sqlite_stat1; // Nothing prints

insert into foo values(1, 2, 3);

insert into foo values(11, 22, 33);

insert into foo values(111, 222, 333);

analyze foo;

select * from sqlite_stat1; // Prints foo||3

insert into goo values(4, 5);

insert into goo values(44, 5);

insert into goo values(444, 555);

analyze goo;

select * from sqlite_stat1; // Prints foo||3 and goo|sqlite_autoindex_goo_1|3 1

The number 3 represents the number of index rows in the table. The number 1 represents average duplicate values in the goo table.

I create an index on three column in table foo: create index foo_index on foo(a, b, c);

then I analyze table foo and select from table sqlite_stat1:

analyze foo;

select * from sqlite_stat1; // Prints out foo|foo_index|3 1 1 1 and goo|sqlite_autoindex_goo_1|3 1

The additional fields are the average of duplicate values in the columns.

(Edited: 2020-03-18)

create table foo(a int, b int, c int); create table goo(c int primary key, d int); select * from sqlite_stat1; // Prints out error: “no such table: sqlite_stat1” analyze foo; select * from sqlite_stat1; // Nothing prints analyze goo; select * from sqlite_stat1; // Nothing prints insert into foo values(1, 2, 3); insert into foo values(11, 22, 33); insert into foo values(111, 222, 333); analyze foo; select * from sqlite_stat1; // Prints foo||3 insert into goo values(4, 5); insert into goo values(44, 5); insert into goo values(444, 555); analyze goo; select * from sqlite_stat1; // Prints foo||3 and goo|sqlite_autoindex_goo_1|3 1 The number 3 represents the number of index rows in the table. The number 1 represents average duplicate values in the goo table. I create an index on three column in table foo: create index foo_index on foo(a, b, c); then I analyze table foo and select from table sqlite_stat1: analyze foo; select * from sqlite_stat1; // Prints out foo|foo_index|3 1 1 1 and goo|sqlite_autoindex_goo_1|3 1 The additional fields are the average of duplicate values in the columns.

-- Mar 18 In-Class Exercise

Resource Description for 1584576997788.jpg

I think the second value in the stat column of sqlite_stat1 keeps track of the average number of rows each unique key points to for that particular index, it will take the ceiling, for example, if there are 6 rows in the table and 5 different key values for an index, the second value will be 2 because ceiling(6/5) = 2.

(Edited: 2020-03-18)

((resource:1584576997788.jpg|Resource Description for 1584576997788.jpg)) I think the second value in the stat column of sqlite_stat1 keeps track of the average number of rows each unique key points to for that particular index, it will take the ceiling, for example, if there are 6 rows in the table and 5 different key values for an index, the second value will be 2 because ceiling(6/5) = 2.

-- Mar 18 In-Class Exercise

 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), (4,5,6), (7,8,9);
 sqlite> ANALYZE FOO;
 sqlite> SELECT * FROM sqlite_stat1;
 FOO||3
 sqlite> INSERT INTO GOO(C,D)
    ...> VALUES (10, 11), (12, 11), (13, 14);
 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

 The second value is an estimate of the number of rows with the same value in the indexed column. The additional 
 values are the estimates of the number of rows with the same value in the other indexed columns.

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), (4,5,6), (7,8,9); sqlite> ANALYZE FOO; sqlite> SELECT * FROM sqlite_stat1; FOO||3 sqlite> INSERT INTO GOO(C,D) ...> VALUES (10, 11), (12, 11), (13, 14); 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 The second value is an estimate of the number of rows with the same value in the indexed column. The additional values are the estimates of the number of rows with the same value in the other indexed columns.

-- Mar 18 In-Class Exercise

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: near ")": syntax error 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> analzye foo; Error: near "analzye": syntax error sqlite> analyze foo; sqlite> select * from sqlite_stat1; foo||1 sqlite> insert into foo values (4,5,6); sqlite> select * from sqlite_stat1; foo||1 sqlite> select * from sqlite_stat1; foo||1 sqlite> insert into foo values (4,5,6); sqlite> analyze foo; sqlite> insert into foo values (4,5,6); sqlite> analyze foo; sqlite> select * from sqlite_stat1; foo||4 sqlite> insert into goo values (1,2); sqlite> analzye goo; Error: near "analzye": syntax error sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|1 1 sqlite> insert into goo values (2,2); sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|2 1 sqlite> insert into goo values (4,6); sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|2 1 sqlite> CREATE INDEX GOO_INDEX ON GOO(D); sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|GOO_INDEX|3 2 goo|sqlite_autoindex_goo_1|3 1 sqlite>

goo|GOO_INDEX|3 2

3 is the number of rows in the index

2 is the number of times a value in the D value will occur in the index

goo|sqlite_autoindex_goo_1|3 1

The 3 is the number of rows.

The 1 is the average number of times a value appears.

(Edited: 2020-03-21)

<nowiki> 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: near ")": syntax error 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> analzye foo; Error: near "analzye": syntax error sqlite> analyze foo; sqlite> select * from sqlite_stat1; foo||1 sqlite> insert into foo values (4,5,6); sqlite> select * from sqlite_stat1; foo||1 sqlite> select * from sqlite_stat1; foo||1 sqlite> insert into foo values (4,5,6); sqlite> analyze foo; sqlite> insert into foo values (4,5,6); sqlite> analyze foo; sqlite> select * from sqlite_stat1; foo||4 sqlite> insert into goo values (1,2); sqlite> analzye goo; Error: near "analzye": syntax error sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|1 1 sqlite> insert into goo values (2,2); sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|2 1 sqlite> insert into goo values (4,6); sqlite> select * from sqlite_stat1; foo||4 goo|sqlite_autoindex_goo_1|2 1 sqlite> CREATE INDEX GOO_INDEX ON GOO(D); sqlite> analyze goo; sqlite> select * from sqlite_stat1; foo||4 goo|GOO_INDEX|3 2 goo|sqlite_autoindex_goo_1|3 1 sqlite> </nowiki> goo|GOO_INDEX|3 2 3 is the number of rows in the index 2 is the number of times a value in the D value will occur in the index goo|sqlite_autoindex_goo_1|3 1 The 3 is the number of rows. The 1 is the average number of times a value appears.

-- Mar 18 In-Class Exercise

create table foo(a int, b int, c int); create table goo(c int primary key, d int); select * from sqlite_stat1; Error: no such table: sqlite_stat1 analyze foo; Select * from sqlite_stat1; Nothing happens or pops up. analyze goo; Select * from sqlite_stat1; Nothing happens or pops up. insert into foo value(0,0,0); insert into foo value(1,1,1); insert into foo value(5,5,5); analyze foo; select * from sqlite_stat1; foo::3 insert into goo values(0, 1); insert into goo values(7, 1); insert into goo values(5, 0); analyze goo; select * from sqlite_stat1; foo: goo:sqlite_autoindex_goo_1:3 1 create index mark on goo(d); analyze goo; The 2nd value of the stat column in sqlite_stat1 keeps track of the index. The first field in the stat column of sqlite_stat1 records the number of rows in the index, and the nth field from the 2nd onwards keeps track of the number of rows that have the same value for the first n-1 columns.