Does MySQL's LIMIT keyword guarantee order of returned data?

Question

I am new to MySQL, but I have used Oracle for some time. I am a little confused about MySQL's LIMIT keyword. Does this keyword imply that the order of the returned data will be the same every time the query is executed? What if more data is added to the tables that affect the query? And how would the data be ordered if no order-by clause exists?

Answer 1

No, LIMIT is non-deterministic. The ordering depends on the data, what indexes are used, internal changes between mysql versions, order of data on disk etc.

Usually you'd combine LIMIT with an ORDER BY to avoid this.

Answer 2

Unless you specify ORDER BY, there are no guarantees about what rows will be returned first.

But in practice, the order will typically match the clustered index order, and will not vary between calls. Don't rely on this behaviour.

Answer 3

LIMIT will not impose any ordering on the returned data.

There is no guarantee that limit will return the same order each time it is executed unless an ORDER BY clause is used. If data is inserted this could affect the results of both the ordered and unordered results.

If you omit an ORDER BY clause, it's basically up to the database to decide the ordering and it is probably based on how the table is indexed. This will change over time as the table is re-indexed and/or more data is added.