MySQL row order for "SELECT * FROM table_name;"

Question

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)? Why? Why not?

Assume the following table structure:

CREATE TABLE table_name (
  foo1 mediumint(8) unsigned NOT NULL default '0',
  foo2 mediumint(8) unsigned NOT NULL default '0',
  foo3 mediumint(8) unsigned NOT NULL default '0',
  foo4 mediumint(8) unsigned NOT NULL default '0',
  UNIQUE KEY (foo1, foo2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Answer 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.

Answer 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.

Answer 3

No, definitely not. Depending on the database engine you're using (ISAM or InnoDB), table structure will generally be some kind of b-tree to allow for faster searching through rows. This will have nothing to do with insertion order, and more to do with how the database constructs an index based on the primary key (or in the case of no key, how a table heap is stored).

Answer 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.

Answer 5

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.