Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / SQLite Новый топик    Ответить
 Простой запрос (поиск страны по IP) сильно тормозит  [new]
amsdev
Member

Откуда:
Сообщений: 44
Подскажите плз: есть задача поиска страны по IP адресу, используя справочник такого вида:

CREATE TABLE IF NOT EXISTS GeoIP (
ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
IpFrom  BIGINT NOT NULL,
IpTo BIGINT NOT NULL,
Country VARCHAR (25) NOT NULL);

CREATE UNIQUE INDEX IF NOT EXISTS IpRangeIdx ON GeoIP (IpFrom,IpTo);
CREATE UNIQUE INDEX IF NOT EXISTS IpRangeEndIdx ON GeoIP (IpTo);


Так же пробовал создавать covering index (IpFrom,IpTo,Country).
Так же пробовал дополнительно создавать отдельные индексы для IpFrom и IpTo

Ну и для поиска используется простой запрос запрос вида:

SELECT Country FROM GeoIP WHERE IpFrom<=? AND IpTo>=?


Проблема в том, что удается делать максимум 6000 поисков в секунду (Windows 10, Core i7), что крайне мало (

EXPLAIN QUERY PLAN выдает: SEARCH TABLE GeoIP USING INDEX IpRangeEndIdx (IpTo>?)
Т.е. почему-то используется только один индекс только для второго значения.

При этом тестовый запрос вида
SELECT Country FROM GeoIP WHERE IpFrom=? AND IpTo=?

выполняется раз в 10-15 быстрее.

Наверняка можно как-то сделать быстрее! Но пока после 3 часов экспериментов не получается...

Помогите плз ! Уж вы то точно знаете как надо ! )))
14 авг 18, 13:12    [21641784]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
Dima T
Member

Откуда:
Сообщений: 12753
Если это диапазоны IP адресов, то должно быть
1. Всегда IpFrom <= IpTo
2. Диапазоны не пересекаются

Если так, то конкретный IP адрес можно проверить так
SELECT top 1 Country FROM GeoIP WHERE IpFrom<=? AND IpTo>=? order by IpFrom
14 авг 18, 13:33    [21641814]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
Dima T
Member

Откуда:
Сообщений: 12753
Еще так попробуй
... order by IpTo
14 авг 18, 13:38    [21641818]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
amsdev
Member

Откуда:
Сообщений: 44
автор
Если это диапазоны IP адресов


Да, именно диапазоны. Содержимое таблицы примерно такое:

IpFrom, IpTo, Country
16777216,16777471,Australia
16777472,16778239,China
16778240,16779263,Australia
16779264,16781311,China
16781312,16785407,Japan
....

К сожалению, LIMIT 1 и OrderBy IpTo не помогли - скорость никак не изменилась.
14 авг 18, 15:00    [21641969]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
Dima T
Member

Откуда:
Сообщений: 12753
Попробуй такой вариант
SELECT top 1 IpFrom, IpTo, Country FROM GeoIP WHERE IpTo>=? order by IpTo

После дополнительно проверить IpFrom<=?
14 авг 18, 15:07    [21641978]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
White Owl
Member

Откуда:
Сообщений: 12309
amsdev
Ну и для поиска используется простой запрос запрос вида:

SELECT Country FROM GeoIP WHERE IpFrom<=? AND IpTo>=?


попробуй
SELECT Country FROM GeoIP WHERE ? between IpFrom AND IpTo
15 авг 18, 04:38    [21642487]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
ShSerge
Member

Откуда: ʚонɔ dиw
Сообщений: 24840
Уберите эти экзисты и нот экзисты. Вы же знаете где экзист, а где нот экзист. Может и поможет. Да, бегин и енд транзакций тоже помогает раз в десять.
15 авг 18, 17:51    [21643478]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
miksoft
Member

Откуда:
Сообщений: 37076
Возможно, поможет - 9308178
15 авг 18, 22:51    [21643772]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
amsdev
Member

Откуда:
Сообщений: 44
автор
Возможно, поможет - 9308178


Большое спасибо, помогло !

Если переписать запрос вот так:

SELECT Country FROM (SELECT Country, IpTo FROM GeoIP WHERE ?>=IpFrom ORDER BY IpFrom DESC LIMIT 1) sub WHERE ?<=sub.IpTo

То получается в 36 (в тридцать шесть) раз быстрее !!!

Огромное спасибо за помощь ! Сам бы ни в жизнь не допер что с подзапросом будет быстрее !
16 авг 18, 14:34    [21644560]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
Dima T
Member

Откуда:
Сообщений: 12753
amsdev, я почти тоже самое предлагал 21641978, только допроверка в коде.
16 авг 18, 14:36    [21644561]     Ответить | Цитировать Сообщить модератору
 Re: Простой запрос (поиск страны по IP) сильно тормозит  [new]
amsdev
Member

Откуда:
Сообщений: 44
Все помогли, всем огромное спасибо !
16 авг 18, 17:16    [21644823]     Ответить | Цитировать Сообщить модератору
Все форумы / SQLite Ответить