Argh, I just discovered that gmail was sending all sqlite mails to spam :( Thank you all for the answers!
Clemens, R Smith: Sorry, I should have given a full production query. Here's an example: SELECT "id" AS _3,"json" AS _5,ifNull(json_extract(json, '$.name'),'') AS _0 FROM "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') ORDER BY _0,_3 LIMIT 10 SELECT COUNT(*) as t from "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') And when I then want to continue from the agent Al with id 123: SELECT "id" AS _3,"json" AS _5,ifNull(json_extract(json, '$.name'),'') AS _0 FROM "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%')AND((_0>='Al' AND (_0!='Al' OR _3>123))) ORDER BY _0,_3 LIMIT 10 SELECT COUNT(*) as t from "agents" tbl WHERE(ifNull(json_extract(json, '$.name'),'') LIKE '%a%') heribert: I prefer using queries like these because if you use offset, the engine needs to skip rows one by one, and if you instead keep the order-by values the engine can use the index to find the next set of results. For small databases it doesn't matter of course. On Mon, Jun 4, 2018 at 6:00 PM heribert <herib...@scharnagl.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
