CODEWITHSUNDEEP
mysql
Jinesh
Jinesh

Reputation: 841

"order by" taking too much time in mysql

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

Upvotes: 4

Views: 9298

Answers (7)

Mehraab Hossain
Mehraab Hossain

Reputation: 68

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.

Upvotes: 0

joe
joe

Reputation: 35087

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

Upvotes: 0

shantanuo
shantanuo

Reputation: 32336

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.

Upvotes: 0

Gav
Gav

Reputation: 11460

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

Generally, indexname is the same as columnname.

Upvotes: 1

Martin B
Martin B

Reputation: 24140

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

Upvotes: 8

Greg Hewgill
Greg Hewgill

Reputation: 993303

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.

Upvotes: 1

Philippe Leybaert
Philippe Leybaert

Reputation: 171814

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

Upvotes: 5

Related Questions