On Sat, Jan 10, 2009 at 04:33:19PM -0500, Igor Tandetnik wrote:
> "Lukas Haase" <lukasha...@gmx.at> wrote in
> message news:gkat07$n2...@ger.gmane.org
> > "SELECT keyword FROM keywords ORDER BY keyword LIMIT %d, %d", iFrom,
> > iTo-iFrom."
> >
> > I use an SQLite database to fill a virtual list control in Windows. In
> > this control, I just tell the control the numer of my elements and the
> > control tells me for which range data is needed.
>
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
One thing I've done before is to use a rowid to track each "page" of
results:
SELECT rowid, keyword
FROM keywords
WHERE rowid > :pagestart ORDER BY keyword LIMIT :pagesize
then remember the last rowid from the result set and use it to start the
next result set.
If you have a JOIN then you can use this trick for one table in the
query, just pick it carefully.
Paging backwards efficiently is not as easy. You want to come up with a
query that can start at some rowid and scan _backwards_ through the
table. This:
SELECT rowid, keyword
FROM keywords
WHERE rowid < :pagestart ORDER BY keyword LIMIT :pagesize
doesn't do what you want, of course (EXPLAIN will show that it uses the
Next instruction instead of Prev).
This:
SELECT rowid, keyword
FROM (SELECT rowid, keyword
FROM keywords
WHERE rowid < :pagestart
ORDER BY rowid DESC LIMIT :pagesize)
ORDER BY
keyword;
does use the Prev instruction, instead of Next, to scan the table in the
sub-select, so it will process the fewest possible rows.
Using rowid makes this very fast. I'm surprised that the wiki page for
scrolling cursors doesn't mention this.
Incidentally, I think many, if not all queries that have an outer table
scan, and many, if not all joins that have an inner table scan but not
an outer table scan, could be programmatically modified to create a
scrolling cursor, forward and backwards. All that has to be done is:
pick one table whose rowid to extract, add the where clause for rowid <
or > than some variable, add the limit clause, and the sub-select for
paging backwards. But parsing the select just do that is hard, while
the SQLite3 parser is uniquely positioned to do it for the user. So
perhaps there's a small RFE here?
Cheers,
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
