Are Tables automatically sorted ascending/descending?

Posted on 2006-05-09
Medium Priority
Last Modified: 2008-02-20
I just noticed recently that when I add values into a mySQL Table and then generate a select statement the values are automatically ordered from smallest to highest of the key value.  Is that something that has always been in mySQL?  or is that new to mySQL 5.0

I've just always assumed that we should consider a database table to be unordered.  But that we can make it ordered by using the SORT BY and other similar commands.

Question by:foobarr
LVL 19

Expert Comment

by:Kim Ryan
ID: 16645511
Did you create your key using auto_increment primary key? If so then naturally the keys will be an ascending series of integers. As keys must be unique, it makes sense for them to be represented this way. So I think rows are being returned in order of  insertion but this is logically the same  as  the key order.

Assisted Solution

TonyBliar earned 500 total points
ID: 16646983
It could be a coincidence; they keys (for example dates) are "de facto" sequential
The other possibility is that you have a clustered index which physically sorts the data at insertion time
And you are correct, you should never *assume* the data is sorted unless you *know* otherwise (eg the presence of a clustered index)

Hope that helps
LVL 16

Assisted Solution

ellandrd earned 500 total points
ID: 16647427
>>then generate a select statement the values are automatically ordered from smallest to highest of the key value.

backing up what teraplane pointed out, it depends on if you use "auto increment" for your PK. if so then yes:


you can also decide how you want ot display your results descending or not by a simple addon to your statement:

SELECT * FROM tblUsers WHERE uid = 1 ORDER BY ASC or
SELECT * FROM tblUsers WHERE uid = 1 ORDER BY DESC ....

>>Is that something that has always been in mySQL?  or is that new to mySQL 5.0

always been in mysql

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 16647819
I see well the thing is I never set the auto increment in my primary keys...

I know with DB2 once you add a row it just gets added to the last line and when you select all from that table that new row you added is still at the bottom...

LVL 16

Expert Comment

ID: 16647826
setting it is considered good practice...
LVL 16

Expert Comment

ID: 16648496

have you found your answer?
LVL 22

Accepted Solution

NovaDenizen earned 1000 total points
ID: 16648794
Try dropping all the indexes on your table, then select them again.  That ought to give you the true ordering of the rows.

I think I've seen mysql using the primary key even when it doesn't need to, so that might be happening here.

Anyway, the by-the-book answer is no, you can't and shouldn't depend on mysql writing the records in any particular order.  Using OPTIMIZE TABLE on a MyISAM table will act like the "SORT BY" command you're talking about (if I understand it correctly), but there's no such explicit command for mysql in general.  You can't influence an InnoDB table on that low a level.
LVL 16

Expert Comment

ID: 16658894
;-) thank you!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month15 days, 7 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question