Форум пользователей MySQL

megamanx · 02.07.2012 10:49:56

Я работаю с geocity lite БД, это база ip, плюс местоположение, многие пользовались наверное. Там ip даны в виде интервалов. Так вот, необходим был быстрый поиск с сравнением типа between. На B-tree скорость просто убогая (на 1,8 млн. примерно 7 секунд, индексы пробовал и на каждый ip, и на оба вместе). Сделал так - создал фиктивный квадрат в geometry polygon, с координатами ((minIP, 0) (minIP 2) (maxIP 2) (maxIP 0) (minIP 0)), и стал вести поиск новой точки в boundary как POINT(currentIP, 1). Скорость стала высокой (порядка 0,0001 сек.). У меня возникло сразу несколько вопросов.
1. Это изврат, очень неприлично выглядит. Может быть, есть другой способ навесить индекс на поля maxIP и minIP, чтобы не работать с Geomerty, о которых я ещё не додумался?
2. Когда создаю фиктивный "квадрат" с координатами ((minIP 0) (maxIP 0) (minIP 0)) опять скорость падает, хотя ищет всё также верно. С чем это связано? Достраивание до прямоугольника, нахождение макс. прямоугольника, который вмещает в себя фигуру?
P.S. Вариант с "разложением" всех ip из интервалов в сплошной список я рассматривал, размер становится жутким.

Отредактированно megamanx (02.07.2012 10:52:06)

rgbeast · 02.07.2012 16:32:35

Интересное решение с geometry.

Можно воспользоваться тем, что интервалы не пересекаются и искать ближайший таким способом:

SELECT * FROM `city` where maxIP > $ip ORDER BY maxIP limit 1;

При этом потребуется отдельно проверять, что ip входит в этот интервал, так как бывает, что ip вне всех интервалов (например, локальные ip)

megamanx · 03.07.2012 11:30:34

Угу, так работает точно с такой же скоростью.

select o.city as `ip.city`, o.latitude as `ip.latitude`, o.longitude as `ip.longitude`, cn.name as `ip.country`, rn.name as `ip.state`

from overal o, region_names rn, country_names cn

where contains(rect, GeomFromText('point(994007574 1)'))

and cn.short_name=o.country_id and rn.country=cn.short_name and rn.id=o.region_id

и

select o.city as `ip.city`, o.latitude as `ip.latitude`, o.longitude as `ip.longitude`, cn.name as `ip.country`, rn.name as `ip.state`

from overal o, region_names rn, country_names cn where o.endIpNum >= '994007574'

and cn.short_name=o.country_id and rn.country=cn.short_name and rn.id=o.region_id

ORDER BY o.endIpNum limit 1;

Во втором случае может возникнуть проблема в связи с введением неправильного значения, но это уже придирки.
спасибо за решение.

Отредактированно megamanx (03.07.2012 12:58:48)

megamanx · 03.07.2012 13:12:37

http://habrahabr.ru/post/125467/ кстати в тему, недавно наткнулся. Использование geometry лучше, чем order by limit 1.

Форум пользователей MySQL

#1 02.07.2012 10:49:56

R-tree, Geometry

#2 02.07.2012 16:32:35

Re: R-tree, Geometry

#3 03.07.2012 11:30:34

Re: R-tree, Geometry

#4 03.07.2012 13:12:37

Re: R-tree, Geometry

Board footer