0

We are using Mysql V5.6. We have a MyISAM table. when we open the table, the records will be displayed in some order instead of primary key order. Even when selecting the records will not be retrieved in primary key order. I am not sure on what basis mysql is displaying the records.

Is there any default order in Mysql, instead of primary key order?

Improve this question
1
  • 2
    No. SQL tables do not have default order. Not in any engine, in any DBMS. Use ORDER BY.
    – ypercubeᵀᴹ
    Commented Sep 17, 2014 at 9:06

1 Answer 1

Reset to default
2

There is no "default" order in the RDBMS. Just because tables are not lists but sets. Imagine records as a cards in a big bag, not in a stack. There is no guarantee that cards will be fetched from the bag in some specific order.

Yes, cards are enumerated by primary key, but you have to request the fetching in that specific order. It is not the default order when order is not specified.

Improve this answer
4
  • Hi Kondybas, but other tables display the data in primary key order by default. I am having this issue in some of my configuration tables(especially MyISAM tables). Also based on your comment I got a doubt, if I didn't specify the order in my select sql, will my result set be retrieved in different order at each retrieval?
    – lourdh
    Commented Sep 17, 2014 at 9:36
  • 1
    Mysql fetch the rows in some "unknown" order, and sometimes that order is an order rows has been inserted/updated but we can't rely on that assumption. If you want rows to be ordered in some way you should specify how rows should be ordered. Otherwise result is completely unpredictable.
    – Kondybas
    Commented Sep 17, 2014 at 9:48
  • 2
    @lourdh Both Kondybas and ypercube are 100% right- tables are sets, not lists, and there is no "ROWID" like in other RDMS. Having said that, for pure optimization, full table scans in MyISAM tend to be done in physical order (roughly the way they were inserted if there were not gaps/deletes) and in primary key order in InnoDB. This is not a rule, just a coincidence, it will change depending on the query and insertion methods, and will vary from server to server. Other engines are even less predictable. Always use ORDER BY.
    – jynus
    Commented Sep 17, 2014 at 10:21
  • @jynus Always use ORDER BY if you really need an ordered result. Sorting can significantly impact the performance while unsorted results are quite equivalent in most cases.
    – Kondybas
    Commented Sep 17, 2014 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.