Results of two queries at once in sqlite?

Question

I am currently trying to optimize the slow process of retrieving a page of log entries from a SQLite database.

I noticed that I almost always get the following entries along with the number 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 ;

along with the total number of records that may match the current query:

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

(to display "page n of m")

I wonder if I can combine the two queries and ask them both in the same sqlite3_exec () just by concatenating the query string. What should the callback function look like? Can I distinguish between different types of data using argc?

What other optimizations would you suggest?

Answer 1

, , UNION count .

.

SQL_CALC_FOUND_ROWS sqlite-users maillist.

: datetime('now'), id , , id DESC. ROWID, .

, time - SQLLite, back-ticks (`).

Answer 2

sqlite3_get_table .

Answer 3

, . .

:

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;

Log_counts ; ...

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

count (*) .