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


Solution:1

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


Solution:2

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


Solution:3

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.


Solution:4

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

Generally, indexname is the same as columnname.


Solution:5

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.


Solution:6

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
Previous
Next Post »