MySQL: Altering ORDER BY

Please explain what this MySQL query is doing and how it can improve performance.  Also, is this a query that can be run once to improve performance or would I need to run this query regularly?
ALTER TABLE `sales` ORDER BY `sale_id`, `sale_time`, `sale_payment_status`, `sale_products_subtotal`, `sale_received`, `sale_refunded`, `sale_contact_id`, `sale_shipping_required`, `sale_shipping_standard`, `sale_shipping_charged`, `sale_fedex`, `sale_tax_method`, `sale_tax`, `sale_custom_credit_description`, `sale_custom_credit_amount`, `sale_payment_status2`, `sale_approval_number`, `sale_notes`, `sale_comments`, `sale_updated`, `sale_status`;

Open in new window

LVL 10
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Alter table ... ORDER BY enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

The table does not remain in the specified order after inserts and deletes.

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.

ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
It sorts the table data by the given order. This is a one time sort process. Thus after further DML order is no longer guaranteed. It only works for tables without a clustered index, cause the clustered index is the physical table structure.

btw, what database engine do you use?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.