Lawrence D'Oliveiro wrote:
> In Bryan Olson wrote:
>
>> coldpizza wrote:
>>> It turned out that the method above ('SELECT * FROM TABLE LIMIT L1,
>>> L2') works ok both with mysql and sqlite3, therefore I have decided to
>>> stick with it until I find something better. With Sqlite3 you are
>>> supposed to use LIMIT 10 OFFSET NN, but it also apparently supports
>>> the mysql syntax (LIMIT NN, 10) for compatibility reasons.

>> A more reliable form is along the lines:
>>
>> SELECT keyfield, stuff
>> FROM table
>> WHERE keyfield > ?
>> ORDER BY keyfield
>> LIMIT 10
>>
>> With the right index, it's efficient.
>
> But that involves keeping track of the right starting keyfield value for the
> next batch of records, which is complicated and nontrivial.

I think you missed the idea here. Recall that we return a
web page showing 10 records and a 'Next' link. We write the
link so that the browser will send back the parameter we
need. If the largest keyfield value on the page is
"Two-Sheds" the link might read:

<A HREF="http://rfh.uk/tablnext.cgi?start=Two-Sheds">Next</A>

The solution is stateless. There's no "keeping track" on the
server side. When we respond to a request, we neither look up
a previous request nor store anything for a future response.


> Simpler to let
> the DBMS do the work for you.

Keeping a cursor with pending data across HTTP requests is
a world of hurt.


--
--Bryan