Быстрый поиск ближайшего местоположения для SQL (MySQL, PostgreSQL, SQL Server)
Может ли кто-нибудь помочь сделать следующий запрос присоединяется вместо подвыборов? Именно из этого урока: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/.
Реализация этого оказывается очень медленной с большим количеством строк (4 миллиона). Я думаю, что подвыборы являются основной причиной, но я не могу понять, как превратить их в объединения.
SELECT
zip,
primary_city,
latitude,
longitude,
distance
FROM
(
SELECT
z.zip,
z.primary_city,
z.latitude,
z.longitude,
p.radius,
p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) AS distance
FROM zip AS z
JOIN
(
/* these are the query parameters */
SELECT
42.81 AS latpoint,
-70.81 AS longpoint,
50.0 AS radius,
111.045 AS distance_unit
) AS p ON 1 = 1
WHERE
z.latitude BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius
ORDER BY distance
LIMIT 15
Решение
Я не верю, что здесь можно что-то сделать. Я полагаю, что ваше длительное время работы исходит из невероятного количества процессора, необходимого для обработки 4 миллионов записей во всех этих вычислениях.
Ваш самый внутренний подзапрос — это всего лишь 4 константы, которые перекрестно соединены с вашим основным подзапросом, поэтому вы ничего не можете сделать здесь, чтобы ускорить его. Это было бы мытье.
Ваш основной подзапрос (большой оператор SELECT) выполняет всю работу здесь и помещается в основной запрос для сохранения обработки, так как расстояние нужно будет вычислять три раза, если оптимизатор mysql не совершит какое-то чудо и не признает, что вычисление было использовал три раза.
В любом случае, здесь, вероятно, худшая версия, которая удаляет самый внешний запрос:
SELECT
z.zip,
z.primary_city,
z.latitude,
z.longitude,
p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) AS distance
FROM zip AS z
JOIN
(
/* these are the query parameters */
SELECT
42.81 AS latpoint,
-70.81 AS longpoint,
50.0 AS radius,
111.045 AS distance_unit
) AS p ON 1 = 1
WHERE
z.latitude BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
WHERE p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) <= p.radius
ORDER BY p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) LIMIT 15
Вы можете видеть, что все случаи z.distance были заменены формулой, которая используется для расчета расстояния в WHERE и ORDER BY части запроса.
Если вы хотели покончить с подзапросом Cross Joined, который содержит константы … вы могли бы сделать это тоже, но теперь вы теряете производительность с последним изменением и теряете читаемость с потерей перекрестного соединения:
SELECT
z.zip,
z.primary_city,
z.latitude,
z.longitude,
111.045 * DEGREES(ACOS(COS(RADIANS(42.81)) * COS(RADIANS(z.latitude)) * COS(RADIANS(-70.81 - z.longitude)) + SIN(RADIANS(42.81)) * SIN(RADIANS(z.latitude)))) AS distance
FROM zip AS z
WHERE
z.latitude BETWEEN 42.81 - (50.0 / 111.045)
AND 42.81 + (50.0 / 111.045)
AND z.longitude BETWEEN -70.81 - (50.0 / (111.045 * COS(RADIANS(42.81))))
AND -70.81 + (50.0 / (111.045 * COS(RADIANS(42.81))))
WHERE 111.045 * DEGREES(ACOS(COS(RADIANS(42.81)) * COS(RADIANS(z.latitude)) * COS(RADIANS(-70.81 - z.longitude)) + SIN(RADIANS(42.81)) * SIN(RADIANS(z.latitude)))) <= 50.0
ORDER BY 111.045 * DEGREES(ACOS(COS(RADIANS(42.81)) * COS(RADIANS(z.latitude)) * COS(RADIANS(-70.81 - z.longitude)) + SIN(RADIANS(42.81)) * SIN(RADIANS(z.latitude)))) LIMIT 15
Итак … в конце концов, это интересное упражнение, но я не думаю, что есть какие-то плюсы, и определенно есть некоторые минусы в этих изменениях.
Другие решения
Других решений пока нет …