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

Reply via email to