On 3/15/17, Donald Griggs <dfgri...@gmail.com> wrote:
>> > Does anyone knows a Common Table Expression (CTE) to be used with the
>> > sqlite_master table so we can count for each table how many rows it
>> > has.
> I wonder if it's always accurate to piggyback on the work of ANALYZE and
> obtain row counts as of the last ANALYZE via:
> select tbl, max(substr(stat, 1, instr((stat || ' '), ' ') -1 )) from
> sqlite_stat1 group by tbl order by tbl;
> Equivalently, if one relies on CAST to obtain the first integer:
> select tbl, max(cast (stat AS NUMERIC)) from sqlite_stat1 group by
> tbl order by tbl;
The current ANALYZE always makes an exact row-count. But there is
code on a branch
(https://www.sqlite.org/src/timeline?r=est_count_pragma) that only
does an approximation. And that "approximate" ANALYZE may land on
trunk within the next release or two.
So, no, I would not trust the sqlite_stat1 data if you need an accurate count.
D. Richard Hipp
sqlite-users mailing list