Are Tables automatically sorted ascending/descending?

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.

LVL 7
foobarrAsked:
Who is Participating?
 
NovaDenizenConnect With a Mentor Commented:
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.
0
 
Kim RyanIT ConsultantCommented:
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.
0
 
TonyBliarConnect With a Mentor Commented:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ellandrdConnect With a Mentor Commented:
>>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:

1st,2nd,3rd,4th,.....n'th

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

0
 
foobarrAuthor Commented:
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...

wierd...
0
 
ellandrdCommented:
setting it is considered good practice...
0
 
ellandrdCommented:
foobarr

have you found your answer?
0
 
ellandrdCommented:
;-) thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.