CODEWITHSUNDEEP
linuxperformancesqlite
Gabriel Schreiber
Gabriel Schreiber

Reputation: 2226

Counting rows in a sqlite db

I have a sqlite db on an ARM embedded platform running Linux with somewhat limited resources. Storage device is a microSD card. Sqlite version is 3.7.7.1. The application accessing sqlite is written in C++.

I want to know the number of rows in several tables in regular intervals. I currently use

select count(*) from TABLENAME;

to get this information. I'm having trouble with the performance: When the table sizes reach a certain point (~200K lines), I have a lot of system and iowait load every time I check the table sizes.

When I wrote this, I though looking up the number of rows in a table would be fast as it is probably stored somewhere. But now I'm suspecting that sqlite actually looks through all rows and when I pass the point where the data doesn't fit into the disk cache anymore I get a lot of io load. This would roughly fit from db size and available memory.

Can anyone tell me if sqlite behaves in the way I suspect?

Is there any way to get the number of table rows without producing this amount of load?

EDIT: plaes has asked about the table layout:

CREATE TABLE %s (timestamp INTEGER PRIMARY KEY, offset INTEGER, value NUMERIC);

Upvotes: 4

Views: 1554

Answers (3)

parity3
parity3

Reputation: 703

Here are 2 possible table row count workarounds (with caveats) that do not cause a table / index scan:

  1. Note for tables where you can use INTEGER PRIMARY KEY AUTOINCREMENT as a primary key, you can grab the count from the sqlite_sequence sqlite meta-table:

    select name,seq from sqlite_sequence

seq will contain either the last id or the next id (I think the last but not sure).

  1. "select max(pkid) from table", which will probably do an index search instead of a scan (and will also only be accurate for tables with no deletions).

Knowing this, if your use case includes UNIQUE deletions for tables you can use AUTOINCREMENT on, you could do a hybrid of the trigger-based solution and only count deleted rows (which would arguably be less bookkeeping than counting the inserts for most scenarios). However if you insert and delete the same row twice this also won't work.

Upvotes: 0

Gabriel Schreiber
Gabriel Schreiber

Reputation: 2226

From all the information I gathered, count() apparently really needs to scan the table. As plaes has pointed out, this is faster if the count is done on a integer indexed column, but scanning the index is still needed.

What I do now is store the row count somewhere and increment / decrement it manually in the same transactions I use to do inserts and deletes to keep it consistent.

Upvotes: 1

plaes
plaes

Reputation: 32736

Does this table have integer index? If not, then add one. Otherwise it has to scan the whole table to count the items.

This is an excerpt of comments from SQLite code that implements COUNT() parsing and execution:

    /* If isSimpleCount() returns a pointer to a Table structure, then
    ** the SQL statement is of the form:
    **
    **   SELECT count(*) FROM <tbl>
    **
    ** where the Table structure returned represents table <tbl>.
    **
    ** This statement is so common that it is optimized specially. The
    ** OP_Count instruction is executed either on the intkey table that
    ** contains the data for table <tbl> or on one of its indexes. It
    ** is better to execute the op on an index, as indexes are almost
    ** always spread across less pages than their corresponding tables.
    */
    [...]
    /* Search for the index that has the least amount of columns. If
    ** there is such an index, and it has less columns than the table
    ** does, then we can assume that it consumes less space on disk and
    ** will therefore be cheaper to scan to determine the query result.
    ** In this case set iRoot to the root page number of the index b-tree
    ** and pKeyInfo to the KeyInfo structure required to navigate the
    ** index.
    **
    ** (2011-04-15) Do not do a full scan of an unordered index.

Also, you can get more information about your query with EXPLAIN QUERY PLAN.

Upvotes: 2

Related Questions