"order by" taking too much time in mysql

Question

"order by" in query is taking to much time in MySQL. SHOW PROFILES shows that the time is taken by the sorting process. Is there any setting/workaround that will decrease the sorting time ?

Answer 1

Adding appropriate indexes for the fields you're ordering by should do the trick.

Answer 2

You may be able to increase the speed of returning sorted results by adding an index on the column(s) that you want your results ordered by.

Answer 3

If you don't have an index on the field that you're ordering by, add one:

"In some cases, MySQL can use an index to satisfy an ORDER BY clause without doing any extra sorting."

Edit: (From the section on ORDER BY optimization in the MySQL documentation.)

Answer 4

Create Index. If u don't have index do that.. it might be helpful

Answer 5

ALTER TABLE `tablename` ADD INDEX `indexname` (`columnname`);

Generally, indexname is the same as columnname.

Answer 6

Can you let me know the output of the following 2 commands: show create table tbl_name explain "your select query"

MySQL will not use index if it thinks that almost all the rows needs to be examined unless there is a covering index. Since only one index per table is used, try to order by the column that is part of that index if it being used at all.