sqlite_stat4 table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite_stat4 table

korablev
DROP TABLE IF EXISTS t1;
create table t1(id primary key, a, b) without rowid;
create index t1a on t1(a);
insert into t1 values(1, 1, 2), (2, 1, 3);
analyze t1;
select * from sqlite_stat4 where idx = 't1a';

t1|t1a|2 1|0 0|0 0|
t1|t1a|2 1|0 1|0 1|

Firstly, there is no blob values for samples(at least they aren't displayed
-- default terminal, macOS Sierra 10.12.6). It always happens if index
consists of integer values. Secondly, I don't understand why table contains
2 rows for 1 value of index key. I mean, statistics in the second row seems
to be wrong: there are 0 rows which are less than 1(for the reason that the
only value in that index is 1). And why do nLt and nDLt columns consist of 2
slots instead of 1(index consists only of 1 column + 1 PK which shouldn't be
taken into account)?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite_stat4 table

Richard Hipp-3
On 10/29/17, korablev <[hidden email]> wrote:

> DROP TABLE IF EXISTS t1;
> create table t1(id primary key, a, b) without rowid;
> create index t1a on t1(a);
> insert into t1 values(1, 1, 2), (2, 1, 3);
> analyze t1;
> select * from sqlite_stat4 where idx = 't1a';
>
> t1|t1a|2 1|0 0|0 0|
> t1|t1a|2 1|0 1|0 1|
>
> Firstly, there is no blob values for samples(at least they aren't displayed
> -- default terminal, macOS Sierra 10.12.6). It always happens if index
> consists of integer values.

Try doing this setting first:

    .mode quote

> Secondly, I don't understand why table contains
> 2 rows for 1 value of index key. I mean, statistics in the second row seems
> to be wrong: there are 0 rows which are less than 1(for the reason that the
> only value in that index is 1). And why do nLt and nDLt columns consist of 2
> slots instead of 1(index consists only of 1 column + 1 PK which shouldn't be
> taken into account)?

See https://www.sqlite.org/fileformat.html#the_sqlite_stat4_table

There are usually 10 to 40 rows per each index.  In your case there
are only two because it is a small table.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite_stat4 table

korablev
Richard Hipp-3 wrote
> See https://www.sqlite.org/fileformat.html#the_sqlite_stat4_table

Thanks, I have already read it, but there is no explanation about this:

korablev wrote
> statistics in the second row seems to be wrong: there are 0 rows which are
> less than 1(for the reason that the only value in that index is 1). And
> why do nLt and nDLt columns consist of 2 slots instead of 1(index consists
> only of 1 column + 1 PK which shouldn't be taken into account)?

Probably the main question is not "why there are 2 samples for 1 value", but
"why there is so strange (if not wrong) statistics?".



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite_stat4 table

Richard Hipp-3
On 10/29/17, korablev <[hidden email]> wrote:
>
> Probably the main question is not "why there are 2 samples for 1 value", but
> "why there is so strange (if not wrong) statistics?".
>

STAT4 is designed to help SQLite run faster for queries of tables with
millions of rows.  For a table containing just two rows, it doesn't
really matter.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users