Hi Helen,

> Here's what the SELECT FIRST n SKIP m syntax does under the hood:
>
> 1. Gets the entire set specified by the WHERE clause and creates a
> temporary set (in memory on disk, depending on size and resources)
>
> 2. Physically orders the temporary set according to the ORDER BY clause.
>
> 3. Starts outputting rows from the temporary set, skipping the first
> m rows if SKIP is specified, until n rows have been sent
>
> 4. Ignores the rest of the temporary set

Interesting, I didn't know that. I think for the tables I normally used
FIRST/SKIP on (of course always with some ORDER BY clause) it didn't
make a huge difference, because I never have more than a few 1000 rows
to scroll through.
I was thinking about using a cursor, but in a web application that would
mean I need to keep the cursor (and the database connection) in the
respective user's session. I'm talking about Java here, but should be
not much different in PHP. This is a big problem, because you can never
know when or if a user would do his next click, it could be in 10
seconds, it could be in 20 minutes or he/she could just decide to close
his browser. In the case the browser was closed I would hold an open
transaction on the database for maybe a very long time, until the
session gets garbage collected (if ever) and I'm even not sure if that
would really close the connection.
So to make my story short: What would you suggest in such a situation?
Is FIRST/SKIP appropriate or is there some magical way to keep a cursor
without keeping the connection to the database? Since web applications
with MySQL usually make extensive use of the ROWS clause I thought it
would be the "right tool for the job (tm)" to use FIRST/SKIP.

thanks,
Michael