IP-Geo. Оптимизация SQL запроса

[zapimir]

Вы что прикалываетесь 0,15 секунд искать IP юзера? :)

Вот советую почитать, зря вы убрали вложенный запрос, оно то хоть и выглядит сложнее, но работает намного быстрее. Ну и по сравнению с бинарными базами Maxmind или Sypex Geo MySQL слишком медленный.

Кроме того у Вас база мягко говоря не оптимальная.
Зачем для IP использовать тип BIGINT? Чтобы таблицы и индексы были в 2 раза больше? Или зачем INT для индекса стран и городов?

[REZ1DENT3]

Выше я описал, что использую не собственную базу. А из статьи habrahabr.ru/post/193024/. Спасибо за ссылки, обязательно посмотрю)

[zapimir]

Просто в таком случае вообще непонятно, чему вы хотели научить новичков. Структура таблиц далеко не оптимальна (не обращаете внимание на важность выбора типов данных, особенно для индексируемых полей), никакого разбора запросов с помощью EXPLAIN, запросы меняете, чтобы они были «читабельнее и короче», а не чтобы быстрее работали.

[dinikin]

Что бы научить новичка оптимизировать запросы, ему нужно объяснить, что такое индексы, как они подхватываются при разных запросах и научить пользоваться командой EXPLAIN. А то, что вы описали, бесполезно для новичка.

[demimurych]

И даже чуть-чуть вредно.

[CoolWolf]

А зачем вы в примерах кода на PHP используете mysql_* функции? Они давно уже deprecated.

[REZ1DENT3]

PHP код из примера, я его не модифицировал. Поэтому mysql_*. Сам работаю, почти, всегда с PDO.

[netload]

> Данная Geo-IP база очень старая

В базе данных есть скрипт обновления. Можете обновлять хоть каждый день.

> Избавимся от подзапроса

Можете объяснить логику такого решения?

Скажу честно. я офигел. В статье «Оптимизация SQL запроса» нет ни слова про EXPLAIN. Вместо 4 быстрых запросов сделан 1 медленный. Зачем, какой в этом смысл?

[REZ1DENT3]

В базе данных есть скрипт обновления. Можете обновлять хоть каждый день.

Согласен, архив net_update не смотрел.

Можете объяснить логику такого решения?

В моей задаче нужно было определить город и страну одновременно. Если города не определён нет, то и выводить страну мне, по задаче, не нужно. И да, это я забыл написать в статье.

Я замерял скорость выполнения PHP + SQL примера из example.php. Скорость выполнения примерно ~0.2997 ms (средне арифметически). С данным запросом, в статье он обозначен #8, код выполняется за ~0.1713 ms (средне арифметически) на моей машине, на VPS сервере разница более мелкая, но ощутимая.

В статье «Оптимизация SQL запроса» нет ни слова про EXPLAIN.

EXPLAIN сначала планировал добавить. Но потом просто не было времени подготовить материал. Возможно, если будет время, я опишу принципы анализа и оптимизации SQL запроса с реальными примерами из кода и добавлю в статью или напишу новую.

Спасибо за критику. Первая статья и не все описал подробно и доходчиво. В будущем планирую исправляться и делать более полные статьи.

[FractalizeR]

Тайминг — миллисекунды или все же секунды? ~0.1713 ms и ~0.1713 s совсем разные вещи. На скринах у вас, как я понимаю, все же секунды.

[REZ1DENT3]

Да, опечатка) Секунды)

[netload]

> В моей задаче нужно было определить город и страну одновременно

Вы не разобрались, зачем нужен подзапрос. Попробуйте разобраться сейчас. Сделайте EXPLAIN для моего запроса и для вашего запроса.

[baltazorbest]

А можете мне объяснить зачем вообще хранить IP пользователя или его страну/город в базе? Берете тот же самый sypex geo или GeoIP maxmind и налету определяете по его IP его город или страну. Зачем вообще использовать базу там где это не нужно? По поводу explain то же верно, наугад делать оптимизацию запросов это как минимум странно, а если это не указывать в статье то как новичок вообще поймет как вы оптимизировали запрос? Это уже не говоря о том что можно вообще использовать nginx + geoip

[dstarcev]

Самое интересное в данной задаче то, что нельзя просто так взять и построить индекс для быстрой выборки по двум столбцам, которые являются границами диапазона.
Если мы делаем индекс по (begin_ip, end_ip), то при запросе BETWEEN сперва поиском по дереву индекса найдется первая строка, у которой begin_ip >= LONG_IP_ADDRESS. А затем начнется последовательное сканирование всех последующих строк с проверкой end_ip <= LONG_IP_ADDRESS. И это медленно.

[dstarcev]

Ошибся в операторах, сначала begin_ip <= LONG_IP_ADDRESS, затем end_ip >= LONG_IP_ADDRESS