database persistence with mysql, sqlite

[Lawrence D'Oliveiro]

In message <NnFKi.2654$6p6.417 at newssvr25.news.prodigy.net>, Bryan Olson
wrote:

> 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.
> 
> 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>

That's assuming keyfield is a) unique, and b) a relevant ordering for
displaying to the user.

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

"limit offset, count" avoids all that.