MySQL: ORDER BY useless?

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.

This isn't directly related to ORDER BY, but be careful using the LIMIT N,M pattern. This is often used for paginating results, e.g.

... limit 0,10;
... limit 10,10;
... limit 20,10;

Each time through mysql will "select" the first N rows and just return the last M to the client. As you go deeper and deeper you're selecting out more and more of rows, only to be thrown away.

In short each "page" takes longer to load. If your datasets are small and queries are using indexes well this may seem inconsequential, but the effect will become evident as you grow. I feel it's generally better to put these constraints in the where clause. e.g.

... where id between 0 and 10;
... where id between 11 and 20;
... where id between 21 and 30;

Finally, if you're worried about the ordering to the limiting effectively creates the correct range restrictions for you then this concern is completely eliminated to begin with.

StackExchange Database Administrators Q#13940, answer score: 2