"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 ?
查询中的“order by”在MySQL中占用了很多时间。 SHOW PROFILES显示分拣过程所花费的时间。是否有任何设置/解决方法可以减少排序时间?
6 个解决方案
#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."
“在某些情况下,MySQL可以使用索引来满足ORDER BY子句,而无需进行任何额外的排序。”
Edit: (From the section on ORDER BY optimization in the MySQL documentation.)
编辑:(来自MySQL文档中关于ORDER BY优化的部分。)
#2
Adding appropriate indexes for the fields you're ordering by should do the trick.
为您订购的字段添加适当的索引应该可以解决问题。
#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.
您可以通过在希望结果排序的列上添加索引来提高返回排序结果的速度。
#4
ALTER TABLE `tablename` ADD INDEX `indexname` (`columnname`);
Generally, indexname is the same as columnname.
通常,indexname与columnname相同。
#5
Can you let me know the output of the following 2 commands: show create table tbl_name explain "your select query"
你能告诉我以下两个命令的输出:show create table tbl_name解释“你的选择查询”
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.
如果MySQL认为几乎所有行都需要检查,除非有覆盖索引,否则MySQL不会使用索引。由于每个表只使用一个索引,因此尝试按照该索引一部分的列进行排序(如果它正在被使用)。
#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.
查询运行缓慢的原因可能是表没有编入索引;如果是这种情况,请创建合适的索引。
#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."
“在某些情况下,MySQL可以使用索引来满足ORDER BY子句,而无需进行任何额外的排序。”
Edit: (From the section on ORDER BY optimization in the MySQL documentation.)
编辑:(来自MySQL文档中关于ORDER BY优化的部分。)
#2
Adding appropriate indexes for the fields you're ordering by should do the trick.
为您订购的字段添加适当的索引应该可以解决问题。
#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.
您可以通过在希望结果排序的列上添加索引来提高返回排序结果的速度。
#4
ALTER TABLE `tablename` ADD INDEX `indexname` (`columnname`);
Generally, indexname is the same as columnname.
通常,indexname与columnname相同。
#5
Can you let me know the output of the following 2 commands: show create table tbl_name explain "your select query"
你能告诉我以下两个命令的输出:show create table tbl_name解释“你的选择查询”
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.
如果MySQL认为几乎所有行都需要检查,除非有覆盖索引,否则MySQL不会使用索引。由于每个表只使用一个索引,因此尝试按照该索引一部分的列进行排序(如果它正在被使用)。
#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.
查询运行缓慢的原因可能是表没有编入索引;如果是这种情况,请创建合适的索引。