Skip navigation links

MySQL Forums :: MyISAM :: What is The Default Sort Order of SELECTS with no ORDER BY Clause?


Advanced Search

Re: What is The Default Sort Order of SELECTS with no ORDER BY Clause?
Posted by: Rick James ()
Date: December 23, 2008 01:39AM

* Do not depend on order when ORDER BY is missing.
* Always specify ORDER BY if you want a particular order -- in some situations the engine can eliminate the ORDER BY because of how it does some other step.
* GROUP BY forces ORDER BY. (This is a violation of the standard. It can be avoided by using ORDER BY NULL.)

SELECT * FROM tbl -- this will do a "table scan". If the table has never had any DELETEs/REPLACEs/UPDATEs, the records will happen to be in the insertion order, hence what you observed.

If you had done the same statement with an InnoDB table, they would have been delivered in PRIMARY KEY order, not INSERT order. Again, this is an artifact of the underlying implementation, not something to depend on.

Options: ReplyQuote


Subject Views Written By Posted
What is The Default Sort Order of SELECTS with no ORDER BY Clause? 25883 Bret Lester 12/21/2008 04:28PM
Re: What is The Default Sort Order of SELECTS with no ORDER BY Clause? 15783 Rick James 12/23/2008 01:39AM


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.