Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select?

(assuming unique alphanumeric index and no auto-increments if these matter)

  • I doubt. The only way "to reorder" records is to alter the index which is clustered - but this action may change the whole execution plan. So the question makes no sense. – Akina Jan 31 at 19:19

No it won't make a difference. You're using the same records to query and order by regardless of the order of the rows. I'm sure there's other tuning metrics that can be assessed if your query(ies) is(are) running slow.

| improve this answer | |

Your question was about a subsequent ORDER BY. The answer is "no". On the other hand, let me give you a case where "performance" may improve.

  • In MyISAM, ALTER TABLE .. ORDER BY .. is a valid option. It changes the "locality of reference". In rare situations it is beneficial.

  • In InnoDB, that ALTER does not make sense if you have a PRIMARY KEY. The PK defines the order of the rows in the table's BTree; that ALTER cannot defeat that.

  • In InnoDB, you can create a table with no PRIMARY KEY, but there will be one provided for you. Let's not get into that; it should not be done.

Here is one example of where "locality of reference" can make some difference. Suppose you have a huge table (bigger than the buffer_pool) containing meta-information about users' photos.

Alternative 1: PRIMARY KEY(photo_id) on a huge table of photos. Since the photos arrive at random times, a single user's photos will be scattered throughout the table.

Alternative 2: PRIMARY KEY(user_id, photo_id), INDEX(photo_id) -- same table. Now all the photos for a single user are clustered together.

Also, let's assume that most queries are like SELECT ... WHERE user_id = 123 AND .... If a SELECT is returning hundreds or rows, case 2 will be faster because of the clustering, which leads to fewer disk reads.

| improve this answer | |

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.