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 d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
