在计算列上订购时的MySQL性能 [英] MySQL-Performance when ordering on calculated column

问题描述

给出的表具有300'000条测试记录.

Given is a table with 300'000 test-records.

我需要做这样的选择:

SELECT (SQRT(POWER(ABS(posts.latitude-$lat),2)+POWER(ABS(posts.longitude-$lng),2))) AS distance
FROM table
ORDER BY distance
LIMIT 0,50

其中$ lat和$ lng随每个请求而变化. 我如何才能加快此类查询的速度?

where $lat and $lng changes for every request. How could I possibly speed up such queries?

谢谢.

推荐答案

实际上，您无法优化该查询.

Actually you cannot optimize that query.

您正在使用计算值对结果进行排序，因此无法使用索引.如果使用explain，您会看到查询的执行方式，并且using temporary将出现在extra列中，这意味着查询中的所有数据都存储在临时表中，在该表中排序被执行.

You are sorting the result using a calculated value, so you cannot use an index. If you use explain you could see how your query in being executed, and using temporary will be present in the extra column, which means that all the data from your query is being stored on a temporary table in which the ordering is performed.

不要紧，只需要查询中的前50个匹配项，它就必须首先获取所有数据，将其转储到临时表中，对该表中的结果进行排序，然后再将前50个返回给您匹配.

It doesn't matter if you only want the first 50 matches in the query, it has first to get all the data, dump it into a temporary table, sort the result in that table and then return to you the first 50 matches.

如您所想，这是一项耗时和耗内存的操作.

As you can suppose, this is a time and memory consuming operation.

因此，最好的选择是在表中放置一个索引，以尽可能快地获取所需的所有行，然后使用php处理它们以获取所需的数据.

So you best option is to place an index in the table to get all the rows you need as fast as you can and then process them with php to get the data you need.

顺便看看 MySQL优化指南.

这篇关于在计算列上订购时的MySQL性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！