3

"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 ?

Jinesh
  • 801
  • 4
  • 13
  • 22

7 Answers7

8

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.)

Martin B
  • 23,670
  • 6
  • 53
  • 72
5

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

Philippe Leybaert
  • 168,566
  • 31
  • 210
  • 223
1

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.

Greg Hewgill
  • 951,095
  • 183
  • 1,149
  • 1,285
1
ALTER TABLE `tablename` ADD INDEX `indexname` (`columnname`);

Generally, indexname is the same as columnname.

Gav
  • 11,062
  • 7
  • 33
  • 35
0

The reason the query is running slowly might be that the table is not indexed; if that is the case, create a suitable index.

LSerni
  • 55,617
  • 10
  • 65
  • 107
joe
  • 34,529
  • 29
  • 100
  • 137
0

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.

shantanuo
  • 31,689
  • 78
  • 245
  • 403
0

you may need to indexing the column on which you want to run order by . you can run the sql 'ALTER TABLE yourTableNameADD INDEXColumnName (ColumnName);'

i had the same problem, and overcame by this procedure.

Mehraab Hossain
  • 68
  • 6