MySQL Forums
Forum List  »  MyISAM

What is The Default Sort Order of SELECTS with no ORDER BY Clause?
Posted by: Bret Lester
Date: December 21, 2008 04:28PM

I am trying to find something definitive in the MySQL developer documentation about this to little avail. What is the default order of SELECTS when no ORDER BY column is specified? If this is mentioned somewhere in the documentation, it has eluded me.

Consider the following simple example table:

CREATE TABLE test (id int primary key, txt char(3));
INSERT INTO test VALUES (1, 'xyz'), (9, 'nno'), (999, 'iik'), (2, 'llz'), (123, 'ooi');

Running a SELECT * FROM TEST seems to always return this data in the order that it was inserted as follows:

mysql> select * from test;
| id | txt |
| 1 | xyz |
| 9 | nno |
| 999 | iik |
| 2 | llz |
| 123 | ooi |
5 rows in set (0.00 sec)

This happens to be exactly what I want to happen but I need to know if this is something I can rely on. I can't find anything in the documentation to placate me. In this specific case I can assume there will be no inserts or updates to the table since it is designed to be a read-only summary table. The question is can i always rely on SELECTS with no ORDER BY coming back in the order they were inserted? And what happens when joins are involved. My testes indicate the insert order is preserved but I can't find anything definitive in the docs about that either. I'd greatly appreciate any insight on this matter! Thanks!

Options: ReplyQuote

Written By
What is The Default Sort Order of SELECTS with no ORDER BY Clause?
December 21, 2008 04:28PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.