What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data:

create table t (
    k int,
    v int,
    index k(k)
    ) engine=memory;

insert into t (k, v)
values (10, 1),
       (10, 2),
       (10, 3);

When issuing select * from t where k = 10 with no order by clause, how does MySQL sort the records by default?

Reposting my answer to a similar question regarding SQL Server:


In the SQL world, order is not an inherent property of a set of data.
Thus, you get no guarantees from your RDBMS that your data will come
back in a certain order -- or even in a consistent order -- unless you
query your data with an ORDER BY clause.

So, to answer your question:

• MySQL sorts the records however it wants without any guarantee of consistency.

• If you intend to rely on this order for anything, you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

This is a property of all SQL, not just MySQL. The relevant text in the SQL-92 spec is:


If an is not specified, then the ordering of the rows of Q is implementation-dependent.

There are similar bits of text in the spec for cursors.

StackExchange Database Administrators Q#6051, answer score: 124