You can certainly get the max and average cell size per page of rows from
dbstat which is the most granular data available I think, as well as the
average and max for all the rows taken together. Assuming that the table is a
"rowid" table, then that is the data for the "leaf" pages only. As in:
select Schema,
Name,
PageNo,
ncell as nrows,
payload / ncell as avglen,
mx_payload as maxlen,
payload as payload,
unused as unused,
pgsize as total
from dbStat
where PageType == 'leaf'
and Schema == 'main'
and Name == 'Details'
union all
select Schema,
Name,
'All',
sum(ncell) as nrows,
sum(payload) / sum(ncell) as avglen,
max(mx_payload) as maxlen,
sum(payload) as payload,
sum(unused) as unused,
sum(pgsize) as total
from dbStat
where PageType == 'leaf'
and Schema == 'main'
and Name == 'Details'
;
The row size will not vary by alot unless the table contains 'text' or 'blob'
column data. The schema and summary for the above table looks like this:
CREATE TABLE Details
(
ApplianceID integer not null references Appliances(ApplianceID) on
delete cascade,
SrcIntID integer not null references Interfaces(InterfaceID) on
delete cascade,
DstIntID integer not null references Interfaces(InterfaceID) on
delete cascade,
Transport text not null collate nocase,
SrcHostID integer not null references Hosts(HostID) on delete cascade,
SrcPort integer not null,
DstHostID integer not null references Hosts(HostID) on delete cascade,
DstPort integer not null,
Action integer not null,
Count integer not null,
FileID integer not null references Files(FileID) on delete cascade
);
schema name 'All' nrows avglen maxlen payload
unused total
---------- ---------- ---------- ---------- ---------- ----------
---------- ---------- ----------
main Details All 11608351 27 31
321835482 1851729 404230144
which indicates that all the rows are about the same size and the average is 27
bytes, which is somewhat less than the 95 bytes max I would expect per row if
the integers were all stored as 8 byte integers.
Are you trying to find rows that have "wacky large" data (as in blob/text)
stored in them?
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of Deon Brewis
>Sent: Friday, 24 January, 2020 21:14
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>No I mean e.g.
>
>row 1 = 500 bytes,
>row 2 = 600 bytes
>row 3 = 80 bytes
>row 4 = 300 bytes
>
>etc.
>
>Like the info that DBSTAT gives, but per row, not per page. This doesn't
>need to be performant - it's for usage analysis during development time.
>
>- Deon
>
>-----Original Message-----
>From: sqlite-users <[email protected]> On
>Behalf Of Simon Slavin
>Sent: Wednesday, January 22, 2020 5:24 PM
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] Row length in SQLITE
>
>On 22 Jan 2020, at 11:44pm, Deon Brewis <[email protected]> wrote:
>
>> Is there any way to get the length of rows in a table / index in
>sqlite?
>
>Do you mean the count of rows in a table / index ?
>
> SELECT count(*) FROM MyTable
>
>There's no easy fast way to do this because SQLite doesn't keep that
>number handy anywhere. It stores the entries in a tree and it would have
>to manually count the leaves of the tree.
>
>----
>
>Or do you mean the count of columns in a table / index ?
>
> SELECT * FROM MyTable LIMIT 1
>
>and count the number of columns returned. Or in C
>
> sqlite3_column_count()
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
