What is MySQL row order for "SELECT * FROM table_name;"?

Assume that the following query is issued to a MySQL database:

SELECT * FROM table_name;

Note that no ORDER BY clause is given.

My question is:

Does MySQL give any guarantees to which order the result set rows will be given?

More specifically, can I assume that the rows will be returned in insertion order?, that is the same order in which the rows were inserted into the table.

Solution 1:

No, there are no guarantees. Unless you specify an order using an ORDER BY clause, the order is totally dependent on internal implementation details. I.e. whatever is most convenient for the RDBMS engine.

In practice, the rows might be returned in their original insertion order (or more accurately the order the rows exist in physical storage), but you should not depend on this. If you port your app to another brand of RDBMS, or even if you upgrade to a newer version of MySQL that may implement storage differently, the rows could come back in some other order.

The latter point is true for any SQL-compliant RDBMS.

Here's a demonstration of what I mean by the order the rows exist in storage, versus the order they were created:

CREATE TABLE foo (id SERIAL PRIMARY KEY, bar CHAR(10));

-- create rows with id 1 through 10
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

DELETE FROM foo WHERE id BETWEEN 4 AND 7;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
+----+---------+

So now we have six rows. The storage at this point contains a gap between rows 3 and 8, left after deleting the middle rows. Deleting rows does not defragment these gaps.

-- create rows with id 11 through 20 
INSERT INTO foo (bar) VALUES
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing'), 
  ('testing'), ('testing'), ('testing'), ('testing'), ('testing');

SELECT * FROM foo;

+----+---------+
| id | bar     |
+----+---------+
|  1 | testing |
|  2 | testing |
|  3 | testing |
| 14 | testing |
| 13 | testing |
| 12 | testing |
| 11 | testing |
|  8 | testing |
|  9 | testing |
| 10 | testing |
| 15 | testing |
| 16 | testing |
| 17 | testing |
| 18 | testing |
| 19 | testing |
| 20 | testing |
+----+---------+

Notice how MySQL has re-used the spaces opened by deleting rows, before appending new rows to the end of the table. Also notice that rows 11 through 14 were inserted in these spaces in reverse order, filling from the end backwards.

Therefore the order the rows are stored is not exactly the order in which they were inserted.

UPDATE: This demonstration I wrote in 2009 was for MyISAM. InnoDB returns rows in index order unless you use ORDER BY. This is further evidence of the point at the start of the answer that the default order depends on the implementation. Using a different storage engine means a different implementation.

Solution 2:

Per this thread, default sort is insert order for MyISAM, and primary key ascending for InnoDB. But I don't think that's a guarantee, just how it's known to work.

Solution 3:

No, you cannot.

Sometimes MySQL will perform select queries with keys you don't expect. Consider this table:

CREATE TABLE `user_permissions` (
  `permId` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `permKey` varchar(16) NOT NULL,
  `permDesc` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`permId`),
  KEY `key_lookup` (`permKey`,`permId`,`permDesc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

MySQL will almost always use the key_lookup key to do any select operation on this table; since permKey is the first field, it will usually end up "sorted" by this key (which appears alphabetical, but isn't exactly).

Without an ORDER BY clause, MySQL (and most/all RDBMS engines) will try to get the data just as it's stored and just as fast as possible.

Solution 4:

From Retrieving Data Using the MySQL SELECT Statement : The SELECT Statement

The data displayed is not ordered. Usually records are retrieved in the same order in which they were inserted into the database

Yes, as per the comment

Although records are normally retrieved in the order in which they are inserted into the database, you cannot rely on a particular order being preserved. If your database is backed up and restored, or if a maintenance operation is performed on the database, MySQL might alter the order in which records are stored internally.