Поиск дубликатов большой геопространственной базы данных

Question

Я работаю с таблицей gemities maxmind ( см. Здесь), в MySQL (Windows).

Эта таблица содержит более 2,7 миллионов строк (что довольно много).

Я пытаюсь найти дубликаты городов (чтобы найти различное написание для каждого города, такого как pekin (fr), пекин...), следуя их долготе и широте.

Даже если я использую левое внешнее соединение, подзапрос или сложное предложение where, но время ответа слишком велико (оно никогда не заканчивается).

Вот моя последняя попытка:

select * 
from cities c1, cities c2 
where c2.longitude=c1.longitude 
and c2.latitude=c1.latitude 
and c2.cities!=c1.cities

У кого-нибудь есть идея?

Answer 1

Вы можете ускорить свой запрос, не выбирая * (Я думаю cities достаточно) и добавление индекса на cities(longtitude,latitude) или даже на cities(longtitude,latitude, cities), Вы также можете попробовать

SELECT longitude,latitude, COUNT(DISTINCT c.cities) as num_dup
FROM cities c
GROUP BY longitude,latitude
HAVING num_dup > 1