On 2018/06/03 1:13 PM, Wout Mertens wrote:
Hi all,
To do paged queries on a query like
SELECT colVal FROM t WHERE b=? LIMIT 10
I keep track of column values and construct a query that will get the next
item in a query by augmenting the query like
SELECT colVal FROM t WHERE b=? AND colVal > ? LIMIT 10
To know how many rows there are in the query, I do
SELECT COUNT(*) FROM t WHERE b=?
Are there any efficiency tricks here? Is it better to run the count before
the query or vice versa?
I assume b is not the Primary Key, (since the use case suggests that it
repeats), but hopefully it is at least an Index.
- If it is not an Index, the entire system is inefficient.
- If it is an Index, then it doesn't matter which comes first[*], the
queries are dissimilar enough to not have differing caching advantages
based on order of execution, except...
- If it is an Index, /and/ the Key repeats magnificently much (Imagine
adding an "Age" column to a phone-book and then filtering on Age, there
will be thousands of people who are all 34, for instance) then you are
better off extracting the set of records to a TEMP table and then
paginating through the temp table's complete dataset and COUNT(*) its
rows. This will be extremely fast, especially if the DB is otherwise
quite hefty, and will allow using the new table's rowids (invisible in
the query) as pagination pegs. Be sure to use a memory-oriented journal
mode and cache settings for this, or if not possible, perhaps even a
second attached in-memory or memory-oriented DB.
[*] - The above assumes there are no FTS tables (or other special
virtual tables) involved, nor any computed Keys - all of which may need
more specific considerations.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
