ip-to-country. как заставить запрос использовать индексы.

camka · 14 Апр 2004

ip-to-country. как заставить запрос использовать индексы.

Есть таблица ip2country

ip_from, ip_to, country

Надо найти строку где заданный ip расположен в промежутке от ip_from до ip_to.

Проблема в том, что не составить запрос, который бы использовал индексы.

Запрос типа
[sql]
select * from ip2country where ip_from <= $my_ip and ip_to >= $my_ip
[/sql]

использует индекс не всегда, потому как часто действует правило когда количество выбираемых строк привышает 30% (это мое подозрение).

Вопрос - можно ли как-то изменить структуру/составить запрос/организовать индексы , что бы запрос на выборку нужной строки использовал индекс:?

Falc · 14 Апр 2004

camka
Как вариант можно хранить адрес сети и искать по нему.

еще можешь попробовать:

SELECT *
FROM ip2country
WHERE ip_to >= $my_ip
ORDER BY ip_to
LIMIT 1

camka · 14 Апр 2004

Автор оригинала: Falc
Как вариант можно хранить адрес сети и искать по нему.

Адрес какой сети?

еще можешь попробовать:

SELECT *
FROM ip2country
WHERE ip_to >= $my_ip
ORDER BY ip_to
LIMIT 1

table type possible_keys key key_len ref rows Extra
------ ------ ------------- ------ ------- ------ ------ -----------
ip range ip_to ip_to 4 (NULL) 47728 Using where

Всего записей в таблице - 47728

Falc · 14 Апр 2004

camka
Убери WHERE ip_to >= $my_ip
и кинь експлаин

camka · 14 Апр 2004

ОК. Все работает. Попробовал взять самые крайние значения.
[SQL]
explain
select * from ip
where ip_to >= 33996344
order by ip_to
limit 1
[/SQL]
table type possible_keys key key_len ref rows Extra
------ ------ ------------- ------ ------- ------ ------ -----------
ip range ip_to ip_to 4 (NULL) 47728 Using where

[SQL]
explain
select * from ip
where ip_to >= 4294967295
order by ip_to
limit 1
[/SQL]

table type possible_keys key key_len ref rows Extra
------ ------ ------------- ------ ------- ------ ------ -----------
ip range ip_to ip_to 4 (NULL) 3 Using where

Причем в независимости от предсказуемого количества рядов из эксплэйна запрос выполняется очень резво резво.

Спасибо.

ecto · 19 Апр 2004

Выложи пожалуйста куда-нить дампик столь полезной вещи)

camka · 20 Апр 2004

Автор оригинала: ecto
Выложи пожалуйста куда-нить дампик столь полезной вещи)

Тут лежит база в csv формате. Кстати в отзывах много указаний на неверные данные даже в последней версии базы.

http://ip-to-country.webhosting.info/node/view/6

А тут, уж простите за нескромность и флуд, я слегка нормализовал структуру базы и заполнил данными.
При вставке данных может возникнуть проблема с размером пакета. Она решается установкой соответствующей директивы в my.ini

PHP:

<?php
/*
CREATE TABLE `country` (
   `id` tinyint(4) unsigned NOT NULL default '0',
   `abbr2` char(2) NOT NULL default '',
   `abbr3` char(3) NOT NULL default '',
   `name` varchar(50) NOT NULL default '',
   PRIMARY KEY  (`id`)
) TYPE=MyISAM

CREATE TABLE `ip` (
  `ip_from` int(10) unsigned NOT NULL default '0',
  `ip_to` int(10) unsigned NOT NULL default '0',
  `country_id` tinyint(3) unsigned NOT NULL default '0',
  KEY `ip_to` (`ip_to`)
) TYPE=MyISAM

select country.* from
ip inner join country on ip.country_id = country.id
where ip.ip_to >= inet_aton('194.204.5.177')
order by ip.ip_to
limit 1
*/

set_time_limit(0);
define('DB_HOST',   'localhost');
define('DB_USER',   'root');
define('DB_PASS',   '');
define('DB_NAME',   'ip_to_country');

define('FILE_PATH', 'ip-to-country.csv');

mysql_connect(DB_HOST, DB_USER, DB_PASS)    or die(mysql_error());
mysql_select_db(DB_NAME)                    or die(mysql_error());

$handle = fopen(FILE_PATH,"r");

$values_ip  = '';
$values_co  = '';

$countries = array();
$i = 1;
while ($data = fgetcsv ($handle, 1000, ","))
{
	if(!isset($countries[$data[2]]))
	{
		$countries[$data[2]] = $cou_id = $i++;
		$values_co .= "(".$cou_id.",'".$data[2]."','".$data[3]."','".mysql_escape_string($data[4])."'),";
	}
	else $cou_id = $countries[$data[2]];

   $values_ip .= "(".$data[0].",".$data[1].",".$cou_id."),";
}

mysql_query("truncate ip");
mysql_query("truncate country");
mysql_query("insert into country values ".rtrim($values_co,','))    or die(mysql_errno().' - '.mysql_error().__LINE__);
mysql_query("insert into ip values ".rtrim($values_ip,','))         or die(mysql_errno().' - '.mysql_error().__LINE__);
?>

Serg12 · 2 Май 2004

Поправка

select country.* from
ip inner join country on ip.country_id = country.id
where ip.ip_to >= inet_aton('194.204.5.177')
order by ip.ip_to
limit 1

читать надо как

select country.* from
ip inner join country on ip.country_id = country.id
where ip.ip_to >= inet_aton('194.204.5.177')
and where ip.ip_tfrom <= inet_aton('194.204.5.177')
order by ip.ip_to
limit 1

-~{}~ 02.05.04 17:14:

и всеравно по этим базам разночтения значительный например с тем же хотлогом...

Вообще кто-нить оценивал актуальность и точность различных GeoIp баз?

Может ссылки какие есть

Falc · 5 Май 2004

Serg12
Твоя поправка вообще к чему?

Serg12 · 5 Май 2004

Моя правка к куску кода от camka

Все работает отлично кроме примера выборки из базы

Falc · 5 Май 2004

Serg12
Во-первых в запросе 2 where быть не может, а во-вторых ты топик читал?

Serg12 · 6 Май 2004

Re: ip-to-country. как заставить запрос использовать индексы.

Объясните тупому, почему это

SELECT *
FROM ip2country
WHERE ip_to >= $my_ip
ORDER BY ip_to
LIMIT 1

правильно работает на базе http://ip-to-country.webhosting.info/node/view/6

и не работает на http://www.techinfo.net.ru/ip2ruscity/

Мне уже плохо... формат баз идентичный...

Falc · 6 Май 2004

Serg12
Оно кстати и не должно, в общем случае.
Но во-первых, если если у тебя все адреса вбиты без пропусков, то это будет работать.
И во-вторых проверку ip_from<=$my_ip уже можно сделать после. Смысл заключался в том чтобы сделать выборку нужного адреса максимально быстро.

camka · 7 Май 2004

Re: Re: ip-to-country. как заставить запрос использовать индексы.

Автор оригинала: Serg12
Объясните тупому, почему это

SELECT *
FROM ip2country
WHERE ip_to >= $my_ip
ORDER BY ip_to
LIMIT 1

правильно работает на базе http://ip-to-country.webhosting.info/node/view/6

и не работает на http://www.techinfo.net.ru/ip2ruscity/

Мне уже плохо... формат баз идентичный...

А что подразумевается под "не работает"?

И еще вопрос - будет ли выборка теоретически быстрее, если поставить на ip_to не простой а уникальный индекс?

Falc · 7 Май 2004

camka
Иногда уникальный индекс дает прирост быстродействия, но я пологаю что не в данной случае.

Но тебе ничто не мешает проверить

camka · 7 Май 2004

Автор оригинала: Falc

Но тебе ничто не мешает проверить

Да просто запрос с обычным индексом выполняется буквально сразу. 0.01 сек или даже 0.00 сек. и сравнить не представляется возможным. а если в цикле несколько запросов - то можно напороться на какое-нибудь кеширование.

Falc · 7 Май 2004

camka
Почему бы не увеличить точность измерений?

camka · 7 Май 2004

а это можно как-то сделать в стандартном mysql клиенте или в настройках сервера? искал - не нашел.

PHP:

$st = getmicrotime();
mysql_query($str);
echo getmicrotime()-$st;

а так разницы не видать. Видимо особо не влияет

Serg12 · 7 Май 2004

2 camka
Не работает так:

SELECT *
FROM ip2country
WHERE ip_to >= $my_ip
ORDER BY ip_to
LIMIT 1

на базе http://www.techinfo.net.ru/ip2ruscity/

Говорит, что везде Москва.

с ip_from <= $my_ip работает правильно

Судя по всему у них подсети пересекаются в базе, а в базе от http://ip-to-country.webhosting.info/node/view/6
- нет

ip-to-country. как заставить запрос использовать индексы.

camka

не самка

Falc

Новичок

camka

не самка

Falc

Новичок

camka

не самка

ecto

Новичок

camka

не самка

Serg12

Guest

Falc

Новичок

Serg12

Guest

Falc

Новичок

Serg12

Guest

Falc

Новичок

camka

не самка

Falc

Новичок

camka

не самка

Falc

Новичок

camka

не самка

Serg12

Guest