When MySQL Will Order Data.
This is cut and paste from an email I’m sending to the general mailing list. I’m putting this here so people can add comments. If any of this is wrong or you can think of a situation I didn’t. Add a nice comment 
Here, off the top of my head are situations in mysql where you can trust that the data is ordered in some fasion.
1. Using an order by clause on a query.
2. Using a group by the data will come out in ascending order of the column that was grouped on.
3. alter table order by has been performed and the table hasn’t been modified .
4. select key from t; that uses the ‘Using Index’ in explain will return in the order of the key.
Use order by anyway just to be safe.
May 16th, 2005 at 7:38 pm
You can’t rely on an implicit order of a result set. It’s an internal server matter, and may change. It is of course also storage engine dependent. So what if you choose one storage engine for a table, make your app rely on its current behaviour, and then convert the table to use another engine. Oopsie… just overall a bad idea to rely on this.
The *proper* way (IMHO) to explain result set ordering is:
a) there is no implicit row order in a result set.
b) if you need a specific order, use ORDER BY. This also makes for clear queries.
c) if the server happens to retrieve the rows in the proper order, it knows this and will optimize away the ORDER BY. So there is no redundant step anyway.