Tutorial :“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 ?


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


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


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.


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

Generally, indexname is the same as columnname.


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.


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

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »