Results from two queries at once in sqlite?

Question

I'm currently trying to optimize the sluggish process of retrieving a page of log entries from the SQLite database.

I noticed I almost always retrieve next entries along with count of available entries:

        SELECT time, level, type, text FROM Logs
         WHERE level IN (%s)
         ORDER BY time DESC, id DESC
         LIMIT LOG_REQ_LINES OFFSET %d* LOG_REQ_LINES ;

together with total count of records that can match current query:

        SELECT count(*) FROM Logs WHERE level IN (%s);

(for a display "page n of m")

I wonder, if I could concatenate the two queries, and ask them both in one sqlite3_exec() simply concatenating the query string. How should my callback function look then? Can I distinguish between the different types of data by argc?

What other optimizations would you suggest?

Answer 1

You can use your query with the sqlite3_get_table function to collect the rows
and get the number of result rows.

Answer 2

You can use triggers to keep a count of the number of entries at each level in a separate table. The triggers need to increment the count when an entry is inserted, and decrement the count when an entry is deleted.

Example:

create table Log_counts (level primary key, count);

create trigger Log_insert_trigger after insert on Logs
  for each row
  begin
    update Log_counts set count = count + 1 where level = new.level;
  end;

create trigger Log_delete_trigger after delete on Logs
  for each row
  begin
    update Log_counts set count = count - 1 where level = old.level;
  end;

You will need to initialize Log_counts with the counts per level; in an empty database, for each level...

insert into Log_counts (level, count) values (%s, 0);

Then you will not need a count(*) query for every page of display.

Answer 3

You can make the count query return the same number of columns as the selection query and make a UNION of the count query and the selection one.

The first row of the result set will contain the total count then.

Another possible solution is described in the post about SQL_CALC_FOUND_ROWS from sqlite-users maillist.

And a small notice about your selection query: if you insert records into the Log table with datetime('now'), and id is the auto incremented primary key of the table, then sorting by time is not required and it's enough to sort by id DESC. Since the auto incremented primary key field is an alias to ROWID, you will get a significant performance improvement.

By the way, time is a built in functions in SQLLite, so you should quote the column name with back-ticks (`).