Does LIMIT OFFSET,LENGTH require ORDER BY for pagination?

Question

I have a MyISAM table with 28,900 entires. I'm processing it in chunks of 1500, which a query like this:

SELECT * FROM table WHERE id>0 LIMIT $iStart,1500

Then I loop over this and increment $iStart by 1500 each time.

The problem is that the queries are returning the same rows in some cases. For example the LIMIT 0,1500 query returns some of the same rows as the LIMIT 28500,1500 query does.

If I don't ORDER the rows, can I not expect to use LIMIT for pagination?

(The table is static while these queries are happening, no other queries are going on that would alter its rows).

Answer 1

Like pretty much every other SQL engine out there, MySQL MyISAM tables make no guarantees at all about the order in which rows are returned unless you specify an ORDER BY clause. Typically the order they are returned in will be the order they were read off the filesystem in, which can change from query to query depending on updates, deletes, and even the state of cached selects.

If you want to avoid having the same row returned more than once then you must order by something, the primary key being the most obvious candidate.

Answer 2

You should use ORDER BY to ensure a consistent order. Otherwise the DBMS may return rows in an arbitrary order (however, one would assume that it's arbitrary but consistent between queries if the no rows are modified).