Получить lat / lng из базы данных Postcode и упорядочить по ближайшему первому в PHP / MYSQL

Question

Получить lat / lng из базы данных Postcode и упорядочить по ближайшему первому в PHP / MYSQL

Я создаю сайт с PHP и MYSQL.

Сначала я пытаюсь упорядочить результаты поиска по ближайшему местоположению. У меня есть таблица предметов и таблица предложений. Мне нужно найти ОБА в этих таблицах местоположение товара / сделки, а затем сравнить местоположение с моей базой данных почтовых индексов, получить широту и долготу из соответствующей записи базы данных и отсортировать результаты. Я использую подготовленные заявления PDO.

У меня есть 3 таблицы в моей базе данных. Почтовые индексы, предложения и предметы.

Почтовые индексы

| postcode | lat | lng |

СДЕЛКИ

| id | title | location |

ПРЕДМЕТЫ

| id | title | location |

Вот мой SQL …

SELECT SQL_CALC_FOUND_ROWS *
FROM (
SELECT *
FROM items
UNION
SELECT * FROM deals
) AS allitemsdeals
INNER JOIN (
SELECT
postcodes.*,
(3959 * acos(cos(radians(custom.lat)) * cos(radians(postcodes.lat)) * cos(radians(custom.lng) - radians(postcodes.lng)) + sin(radians(custom.lat)) * sin(radians(postcodes.lat)))
) AS distance
FROM postcodes
INNER JOIN postcodes AS custom
WHERE custom.postcode = ?
) postcodes ON allitemsdeals.location = postcodes.postcode
HAVING distance < 5 ORDER BY id LIMIT ? OFFSET ?

У меня проблема с моим соединением по этому вопросу. Запрос работает, но занимает около минуты! 🙁

Любая помощь будет принята с благодарностью! Спасибо! 🙂

3

geocoding latitude-longitude mysql php

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Подзапросы убивают вашу производительность. Попробуйте предваряющий EXPLAIN на ваше SELECT и глядя на вывод — я подозреваю, что много USING FILESORT а также USING (NULL) INDEX вещи. И много записей проверено. Вероятно, некоторые derived столы тоже …

Всех тех JOIN (SELECT ... инструкции приводят к выполнению запроса, кешированию результатов (или, что еще хуже, повторному запуску для каждой записи в левой части JOIN) без всякой индексации — и все превращается в ужасную остановку.

Наконец то SQL_CALC_FOUND_ROWS — не делай этого, если не нужно. Это приводит к тому, что MySQL вычисляет все строки и обрабатывает все строки просто для их подсчета, прежде чем применять LIMIT, Вам было бы гораздо лучше уменьшить результаты как можно больше еще до LIMIT — и определенно лучше не использовать SQL_CALC_FOUND_ROWS, (Отдельный SELECT COUNT(1) было бы лучше, поверь мне. Был там.)

Решение? Нелегко. Начните с изменения вашей схемы.

Первый: Я бы объединил «предложения» и «предметы» в одну большую таблицу — используйте столбец, чтобы различать типы (если нет других столбцов, о которых я не вижу), потому что UNION вероятно (или определенно?) убьет все ваши индексы.

второй: Этот подзапрос для расстояния — о нет! Это должно быть прямо WHERE пункт для расчета максимального расстояния. Я попытаюсь оптимизировать его, если хотите, — но без SQLfiddle.com или чего-то, с чем можно поиграть, немного начнется тиражирование вашей схемы и данных в мое свободное время. (Если вы настроите SQLfiddle вместе с ним, я с удовольствием послушаю.)

в-третьихЯ бы хотел начать снова с данных long / lat, используя пространственную функциональность MySQL. Доступно много документации:

Это классная штука — тот тип данных, с которым я хотел иметь предлог, чтобы поиграть в течение (оплачиваемых!) Часов сам в течение очень долгого времени. Мне интересно посмотреть, какое решение вы в конечном итоге.

Как я уже сказал, по крайней мере, я с радостью посмотрю следующие 72 часа, если вы предоставите SQLfiddle (при условии, что кто-то другой сначала не предоставит лучшего решения).

0