111

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?

2
  • None of these answers actually answer the question. Yes I know that the order COULD be anything, it COULD change - but what IS it - it will be definable somehow, even if that definition is random by nature of multi-threading race conditions. This is the answer I want because I have existing code that doesn't specify order and I want to know what it does currently.
    – mjaggard
    Dec 22, 2023 at 11:31
  • There is however a an answer here: stackoverflow.com/questions/21829957/… the order appeary to mostly depend on the index used, although I've also seen that it is ordered by disk order which would be harder to predict.
    – mjaggard
    Dec 22, 2023 at 11:38

3 Answers 3

124

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 <order by clause> 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.

0
45

The order of the rows in the absence of ORDER BY clause may be:

  • different between any two storage engines;
  • if you use the same storage engine, it might be different between any two versions of the same storage engine; Example here, scroll down to "Ordering of Rows".
  • if the storage engine version is the same, but MySQL version is different, it might be different because of the query optimizer changes between those versions;
  • if everything is the same, it could be different due to the moon phase and that is OK.
1
  • 3
    Moon phases do affect SQL properties. Otherwise, how would we explain the fact that my query was working a few minutes ago, but now that my project manager is looking at it, it doesn't? 😏
    – davidwebca
    Dec 2, 2022 at 16:55
12

Insertion is unordered, chaotic, on arrival. The index which is created does have order where elements are inserted in proper location in the linked list which is the index. Think of a triply linked list for an index, where you have a forward moving link from one index element to the next, a backward looking link for the traversal and integrity purposes, and then a set of pointers to the actual records in the table which match the indexed element in question.

Actual data, chaotic in storage. Index associated with the data, ordered in storage and construction. Actual pull of data, ordered or unordered, depends upon the query involved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.