I'm using also paged queries. I'm adding an OFFSET to the select-limit
query.
Works for me.
Am 03.06.2018 um 14:16 schrieb R Smith:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
