Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose you have the following table and data:

create table t(k int, v int,index k(k)) engine=memory; 
insert into t 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?

share|improve this question
2  
See this answer. –  Nick Chammas Sep 22 '11 at 5:07
    
Since your OP specified engine=memory, is your question directed at that engine or are you asking for answers across all engines? –  atxdba Sep 22 '11 at 7:08
    
Please explain the reason you are interested in knowing this so we can provide more targeted answers. If you are just asking because you are interested about the internals of MySQL, Rolando's answer is great. –  Nick Chammas Sep 22 '11 at 14:58
    
If, on the other hand, you intend to rely on this order for anything, you must specify your desired order using ORDER BY as I explain below. To do anything else is to set yourself up for unwelcome surprises. –  Nick Chammas Sep 22 '11 at 14:59
add comment

3 Answers

up vote 9 down vote accepted

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 are just curious about the internals of MySQL, Rolando provides an interesting answer.
  • If, on the other hand, 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.
share|improve this answer
    
Your answer is great for SQL Server. For MySQL, I just learned, moments ago, that my answer is too version-specific. @Laurynas Beveinis provided a link from Percona explaining this better. +1 for your answer. –  RolandoMySQLDBA Sep 22 '11 at 15:29
    
@RolandoMySQLDBA - My answer stresses an important point in the relational model--namely, that data sets do not have an ordering. It thus applies to all SQL databases. –  Nick Chammas Sep 22 '11 at 15:40
2  
@RolandoMySQLDBA - From page 373 of the SQL-92 standard: "If an <order by clause> is not specified, then the table specified by the <cursor specification> is T and the ordering of rows in T is implementation-dependent." –  Nick Chammas Sep 22 '11 at 15:43
    
If MySQL was fully following SQL-92 as SQL Server does, my answer would have been different. Your answer fits in a database-agnostic way towards SQL Server, Oracle, and other RDBMSs that hold standards. By default, MySQL throws those standards "under the bus". ANSI complaint stuff can be configured in my.cnf, but no one honestly does that. That's why answers for MySQL may seem off in the face of RDBMSs that are purer and more stringent in SQL and, at the same time, independent of internal dependencies. –  RolandoMySQLDBA Sep 22 '11 at 16:12
    
@RolandoMySQLDBA This answer is correct for the relational model of all SQL databases and as such is "more correct" than mine which was to give a perspective from database engine developer point of view. –  Laurynas Biveinis Sep 22 '11 at 17:40
add comment

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.

share|improve this answer
add comment

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.
share|improve this answer
    
Read the link you provided and adjusted my answer. Thank you for the most updated info. +1 !!! –  RolandoMySQLDBA Sep 22 '11 at 15:24
    
I just looked at your profile. You work with Percona. That's great !!! I am glad to see some MySQL gladiators joining the DBA.SE –  RolandoMySQLDBA Sep 22 '11 at 17:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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