在与WHERE共享的计算上优化MySQL ORDER BY [英] Optimizing MySQL ORDER BY on a calculation shared with WHERE

问题描述

我有一个MySQL SELECT查询，该查询使用WHERE子句中的毕达哥拉斯来计算距离，以将结果限制为一定的半径.

I have a MySQL SELECT query that calculates a distance using Pythagoras in the WHERE clause to restrict results to a certain radius.

我还使用ORDER BY子句中的完全相同的计算来对结果进行最小距离排序.

I also use the exact same calculation in the ORDER BY clause to sort the results by smallest distance first.

MySQL是否计算两次距离(一次是WHERE，一次是ORDER BY)?

Does MySQL calculate the distance twice (once for the WHERE, and again for the ORDER BY)?

如果可以，该如何优化查询，使其仅计算一次(如果可能的话)?

If it does, how can I optimize the query so it is only calculated once (if possible at all)?

推荐答案

MySQL是否计算两次距离(一次在WHERE，一次在ORDER BY)?

Does MySQL calculate the distance twice (once for the WHERE, and again for the ORDER BY)?

否，如果以完全相同的方式编写计算，将不会执行两次.但是，如果您的目标是提高应用程序的性能，那么您可能希望查看更大的图景，而不是着眼于这个次要的细节，这最多可能给您带来两个方面的差异.一个更严重的问题是您的查询阻止了索引的有效使用，并会导致全面扫描.

No, the calculation will not be performed twice if it is written in exactly the same way. However if your aim is to improve the performance of your application then you might want to look at the bigger picture rather than concentrating on this minor detail which could give you at most a factor of two difference. A more serious problem is that your query prevents efficient usage of indexes and will result in a full scan.

我建议您更改数据库，以便使用几何类型并创建 MBRWithin 以便快速找到圆边界框内的点.找到这些点后，您可以仅对这些点进行更昂贵的距离测试.如果您的表很大，并且典型搜索仅返回一小部分行，则此方法将明显更快.

I would recommend that you change your database so that you use the geometry type and create a spatial index on your data. Then you can use MBRWithin to quickly find the points that lie inside the bounding box of your circle. Once you have found those points you can run your more expensive distance test on those points only. This approach will be significantly faster if your table is large and a typical search returns only a small fraction of the rows.

如果无法更改数据模型，则仍然可以通过先使用边界框复选框(例如WHERE x BETWEEN 10 AND 20 AND y BETWEEN 50 AND 60)来提高性能.边界框检查将能够使用索引，但是由于R-Tree索引仅在几何类型上受支持，因此您将不得不使用标准B-Tree索引，对于这种类型的查询，它的效率不高(但仍然很多)比您目前正在做的事情要好).

If you can't change the data model then you can still improve the performance by using a bounding box check first, for example WHERE x BETWEEN 10 AND 20 AND y BETWEEN 50 AND 60. The bounding box check will be able to use an index, but because R-Tree indexes are only supported on the geometry type you will have to use the standard B-Tree index which is not as efficient for this type of query (but still much better than what you are currently doing).

这篇关于在与WHERE共享的计算上优化MySQL ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！