as has been pointed out before on this list, the mysql way is to use the SELECT to create a new temporary table with an auto_increment primary key (the table goes away when your connection does). it's a pretty simple idiom - you only have to specify the index column.

you can now index into this table in constant time, unlike what happens when you use LIMIT, and you aren't locking the original table(s) for the duration of your use of the result set. so you can chunk the rows out, say, 100 at a time, with essentially zero overhead and minimal impact on other processes.

don't be fooled by smoke along the lines of "well, oracle doesn't lock the tables" or "oracle lets you have long-lived result sets". you pay for these things (after you've already paid for larry's jet) by the infamous "rollback segments" that grow without bound (until you hit your resource limit) while the db is waiting for you to finish with your result set. conservation of difficulty.

Michael Thaler wrote: