CREATE TEMPORARY TABLE tt; INSERT INTO tt; many times SELECT FROM tt ORDER BY id LIMIT ?,1000;
The temp table is created at the beginning of the script having an "id" auto_increment column (and others). Part 1 of the script fills up the table using various SELECTs. Part 2 should process all selected rows in chunks of 1000 but will never ever write the table again.
I assume that these two commands are the same
SELECT FROM tt ORDER BY id LIMIT ?,1000; SELECT FROM tt LIMIT ?,1000;
but is this true? Is there any chance that mySQL will retrive the rows in a different order on multiple SELECTs even if the table is not written in between?
Sorting the temp table is the longest part while reading it and I'd be happy to get rid of the ORDER BY part but I don't want to miss a record because the order changed between SELECT LIMIT 0,1000 and SELECT LIMIT 1000,1000.