Имеется таблица
CREATE TABLE `zips` (
`id` char(5) NOT NULL,
`type` varchar(255) NOT NULL,
`city_id` bigint(20) unsigned NOT NULL,
`timezone_id` bigint(20) unsigned NOT NULL,
`area_codes` varchar(255) NOT NULL,
`latitude` decimal(10,8) NOT NULL,
`longitude` decimal(11,8) NOT NULL,
`estimated_population` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `latitude` (`latitude`),
KEY `longitude` (`longitude`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
содержащая 42,153 записи.
В целях оптимизации поиска расстояний появилась идея создать таблицу
CREATE TABLE `zip_distance` (
`zip1` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`zip2` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`distance` decimal(6,2) DEFAULT NULL,
UNIQUE KEY `zips_uni` (`zip1`,`zip2`),
KEY `distance` (`distance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
и просчитать расстояние для каждой пары zip кодов заранее что бы не приходилось пересчитывать его каждый раз при поиске в продакшене.
Получается 42,153 * 42,153 == 1,776,875,409 записей. Что бы для начала прикинуть создал процедуру которая просто добавляет все пары zip кодов в zip_distance:
begin
declare done int default false;
declare zip1 char(5);
declare zip2 char(5);
declare cur1 cursor for select id from zips order by id asc;
declare cur2 cursor for select id from zips order by id asc;
declare continue handler for not found set done = true;
open cur1;
open cur2;
read_loop: loop
fetch cur1 into zip1;
fetch cur2 into zip2;
if done then
leave read_loop;
end if;
insert into zip_distance set zip1 = zip1, zip2 = zip2;
end loop;
close cur1;
close cur2;
end
Результат печальный. Судя по скорости работы процедуре потребуется около года на моём железе.
Можно ли как то оптимизировать что бы просчитать всё максимум за один день? Может быть есть готовая таблица с расстояними? Так же не известно насколько быстрым будет поиск по таблице с почти 2 биллионами записей, возможно ещё медленней и смысла в этом всём вообще нет.
