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)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.
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.
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.
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.