CODEWITHSUNDEEP
mysql
inquam
inquam

Reputation: 12932

Performance cost for using primary key in order by

Let's say I have a table with a three column primary key. If I select all from that table without any order by clause they are, to my understanding, ordered by these columns. The first one, and within that by the second column and within that by the third.

Is there any additional cost, or perhaps performance gain, by explicitly adding a order by clause with the three columns in the order they are part of the primary key?

Upvotes: 1

Views: 760

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52655

If I select all from that table without any order by clause they are, occurring to my understanding, ordered by these columns

Your understanding is incorrect. Without an order by the database engine may output the results in any order it chooses. In fact if you were to add a clustered index on another field it is likely to change the order of the result.

Is there any additional cost, or perhaps performance gain

There will never be a performance gain by ordering (unless you're doing something that effects branch prediction ). There will be no cost if the database was going to order that way. However there may be a correctness issue if you're not specifying the order.

Upvotes: 2

Related Questions