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?

  • 2
    No. SQL tables do not have default order. Not in any engine, in any DBMS. Use ORDER BY. – ypercubeᵀᴹ Sep 17 '14 at 9:06
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.

  • 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 Sep 17 '14 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 Sep 17 '14 at 9:48
  • 1
    @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 Sep 17 '14 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 Sep 17 '14 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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