Are Tables automatically sorted ascending/descending?

Posted on 2006-05-09
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

    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

    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

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

    LVL 7

    Author Comment

    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

    setting it is considered good practice...
    LVL 16

    Expert Comment


    have you found your answer?
    LVL 22

    Accepted Solution

    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

    ;-) thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now