Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

Question

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)

Answer 1

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.

Answer 2

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.