Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Оптимизация поиска по IP

Автор: Nigel 19.11.2008, 15:34

БД MYSQL5. Делаю GeoIp таргетинг на сайте. Решил попробовать руцентровскую базу.
В итоге, поиск веду по следующей таблице

Код


CREATE TABLE `ipgeobase_index` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `ip_interval` (`ip_from`,`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251

Записей в базе порядка 100к.
Запрос вида

Код


SELECT SQL_NO_CACHE * 
FROM `ipgeobase_index` 
FORCE INDEX ( ip_interval ) 
WHERE ipgeobase_index.ip_from <=3277778988
AND ipgeobase_index.ip_to >=3277778988

выполняется СЛИШКОМ ДОЛГО для меня 0.4 сек.
В explain'е следующее

Код


EXPLAIN SELECT SQL_NO_CACHE * 
FROM `ipgeobase_index` 
FORCE INDEX ( ip_interval ) 
WHERE ipgeobase_index.ip_from <=3277778988
AND ipgeobase_index.ip_to >=3277778988

Код


id    select_type    table                     type    possible_keys    key                key_len    ref    rows    Extra 
1    SIMPLE                ipgeobase_index  range    ip_interval    ip_interval    4                NULL    74653    Using where

Убираю индекс

Код


SELECT SQL_NO_CACHE * 
FROM `ipgeobase_index` 
IGNORE INDEX ( ip_interval ) 
WHERE ipgeobase_index.ip_from <=3277778988
AND ipgeobase_index.ip_to >=3277778988

Запрос занял 0.0179 сек
Но в explain'е

Код


id    select_type    table                        type    possible_keys    key    key_len    ref    rows      Extra 
1    SIMPLE                ipgeobase_index     ALL    NULL                NULL    NULL                NULL    109187  Using where

Т.е. идет фулскан, который не есть гуд.
Вопрос, что можно сделать, чтобы уменьшить время выборки.
З.Ы. для сравнения пробовал maxmind'овскую базу, где поиск идет в файле. Время поиска при этом там 0.05 сек.
Но она не точна для России.
З.З.Ы. Просьба модераторам, сделать зеркало в разделе php/базы данных mysql.
А есть раздел, посвященный оптимизации? (я что-то такой не видел на форуме)

Автор: Akina 19.11.2008, 16:02

Во-первых,

Код


WHERE ipgeobase_index.ip_from <=3277778988
AND ipgeobase_index.ip_to >=3277778988

прекрасно трансформируется в

Код


WHERE 3277778988 Between ipgeobase_index.ip_from AND ipgeobase_index.ip_to

Во-вторых, сделайте не один составной индекс, ибо его эффективно использовать невозможно, а два независимых индекса по каждому из полей.

Автор: Nigel 19.11.2008, 16:32

Akina, я это еще в самом начале пробовал.

Код


EXPLAIN SELECT SQL_NO_CACHE * 
FROM `ipgeobase_index` 
FORCE INDEX ( ip_from, ip_to ) 
WHERE 3277778988 
BETWEEN ipgeobase_index.ip_from
AND ipgeobase_index.ip_to

В explain'е

Код


id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra 
1    SIMPLE    ipgeobase_index    ALL    NULL    NULL    NULL    NULL    109187    Using where

Т.е. получается 2-ой вариант, смотрите пост выше.
Да, время 0.01 - устраивает, но идет фулскан.

Автор: Akina 19.11.2008, 17:30

Код


CREATE TABLE `ipgeobase_index` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `ip_1` (`ip_from`),
  KEY `ip_2` (`ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251

SELECT SQL_NO_CACHE * 
FROM `ipgeobase_index` 
FORCE INDEX ( ip_1, ip_2 )  
WHERE ipgeobase_index.ip_from <=3277778988
AND ipgeobase_index.ip_to >=3277778988

дает тот же EXPLAIN?

Автор: Nigel 19.11.2008, 18:09

Нет, в этом случае первый вариант, который в зависимости от айпи от 0.2 до 0.4 сек идет, что не подходит.

Код


id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra 
1    SIMPLE    ipgeobase_index    range    ip1,ip2    ip2    4    NULL    36416    Using where

Автор: Akina 20.11.2008, 10:01

А если создать и использовать только один индекс? скажем ip_1?

Добавлено через 16 секунд
впрочем, получится то же, что и с составным...

Добавлено через 55 секунд
Кстати... а если не форсить индексы - что выбирает парсер? любопытства ради я бы посмотрел...

Автор: Nigel 20.11.2008, 11:21

Akina, в случае составного индекса, если не форсить, он оптимизатор то включает, то выключает, смотря, что ищешь (кстати, именно поэтому я начал форсе делать, так как индекс то добавил, а оптимизатор его игнорировал, умный он, однако)). Если делать 2, то он один из них использует, всегда почти второй.
Интересную вещь нашел.. В результате поиска он всегда выдает 2 айпи адреса, один со статусом ALLOCATED PA, другой - ASSIGNED PA. Т.е. провайдеру выдается сетка айпи адресов, он часть из них раздает местным компаниям. Так вот дописал в запросе LIMIT 2 и...
Запрос занял 0.0005 сек, смотрю explain

Код


id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra 
1    SIMPLE    ipgeobase_index    range    ip_from,ip_to    ip_to    4    NULL    31354    Using where

Получилось))
Вообщем, для этой задачи как я понял лучше использовать 2 индекса, чтобы оптимизатор выбирал подходящий при поиске и юзать лимит.
Кстати, почему так с лимитом получается, может кто-нибудь объяснит? Ведь что с ним, что без него - находится по 2 записи, а скорость поиска - небо и земля.

зы. Кажется, понял, когда он находит требуемые записи ( а их 2), то он дальше не сканит таблицу, а если лимит не указываешь, то ему приходится проверять, а может еще что в поиск попадет...
ззы. темку еще открытой оставлю на пару деньков, может еще что предложат...
Akina, ты не против smile

Автор: Akina 20.11.2008, 12:45

Цитата(Nigel @ 20.11.2008, 12:21

)

Ведь что с ним, что без него - находится по 2 записи, а скорость поиска - небо и земля.

Если есть лимит - как только найдется 2 записи, поиск будет прекращен.

Добавлено через 31 секунду

Цитата(Nigel @ 20.11.2008, 12:21

)

Akina, ты не против

Я тут не модератор, мне сиренево