Will mysql follows any default sorting option in innodb

Question

I need to fetch data in batch wise. Example 1 to 1000, 1001 to 2000

Query: Select * from Employee limit 1, 1000 Select * from Employee limit 1001, 1000

Here no order by is used in this query. Will the second query returns duplicate data? or it will follow any sorting techniques?

Answer 1

This question was previously called a "duplicate" of The order of a SQL Select statement without Order By clause . That is inappropriate as a "duplicate" link because it refers to engines other than MySQL. However, the effect is "correct". That is, you must use ORDER BY; do not assume the table is in some order.

I brought this question back to life because of a more subtle part of the question, referring to a common cause of duplicates.

This

Select * from Employee limit 1001, 1000

has two problems:

• LIMIT without an ORDER BY is asking for trouble (as discussed in the link)
• You appear to be doing "pagination" and you mentioned "returns duplicate data". I bring this up because you can get dups even if you have an ORDER BY. To elaborate...

OFFSET is implemented by stepping over rows.

Between getting N rows and getting the next N rows, some rows could be INSERTed or DELETEd in the 'previous' rows. This messes up the OFFSET, leading to either "duplicate" or "missing" rows.

More discussion, plus an alternative to OFFSET: Pagination It involves "remembering where you left off".

Specific to InnoDB:

• The data's BTree is ordered by the PRIMARY KEY. That is predictable, but
• The query does not necessarily use the "table" to fetch the rows. It might use a 'covering' INDEX, whose BTree is sorted by a secondary key!

For grins... MyISAM:

• The data is initially ordered by when the rows were inserted.
• That order may change as Inserts and Deletes, and even Updates, occur.
• And the query may use a covering index (Primary or secondary).