Довелось разбираться с GeoIP. Начинал с
этого топика, в итоге скачал и распарсил базу maxmind.
Структура таблицы:
CREATE TABLE IF NOT EXISTS `geoip_block` (
`BlockId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`StartNum` int(11) unsigned NOT NULL,
`EndNum` int(10) unsigned NOT NULL,
`LocationId` int(10) unsigned NOT NULL,
PRIMARY KEY (`BlockId`),
KEY `GeoIp_StartNum` (`StartNum`),
KEY `GeoIp_EndNum` (`EndNum`),
KEY `GeoIp_LocationId` (`LocationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3620778 ;
Количество записей:
mysql> SELECT count(BlockId) FROM geoip_block;
+----------------+
| count(BlockId) |
+----------------+
| 3620777 |
+----------------+
1 row in set (0.00 sec)
Выполняю 2 запроса:
mysql> SELECT SQL_NO_CACHE LocationId FROM geoip_block where StartNum<=1385960996 and EndNum>=1385960996 LIMIT 1;
+------------+
| LocationId |
+------------+
| 161 |
+------------+
1 row in set (5.20 sec)
И
mysql> SELECT SQL_NO_CACHE LocationId FROM (SELECT * FROM geoip_block WHERE StartNum <=1385960996 ORDER BY StartNum DESC LIMIT 1) AS T WHERE EndNum >=1385960996;
+------------+
| LocationId |
+------------+
| 161 |
+------------+
1 row in set (0.0006 sec)
Видим, что разница во времени выполнения — огромная.
Explain результата не дает особо:
mysql> EXPLAIN SELECT SQL_NO_CACHE LocationId FROM geoip_block where StartNum<=1385960996 and EndNum>=1385960996 LIMIT 1;
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
| 1 | SIMPLE | geoip_block | range | GeoIp_StartNum,GeoIp_EndNum | GeoIp_StartNum | 4 | NULL | 1810530 | Using where |
+----+-------------+-------------+-------+-----------------------------+----------------+---------+------+---------+-------------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE LocationId FROM (SELECT * FROM geoip_block WHERE StartNum <=1385960996 ORDER BY StartNum DESC LIMIT 1) AS T WHERE EndNum >=1385960996;
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | geoip_block | range | GeoIp_StartNum | GeoIp_StartNum | 4 | NULL | 1810530 | Using where |
+----+-------------+-------------+--------+----------------+----------------+---------+------+---------+-------------+
2 rows in set (0.00 sec)
Вопрос: Как MySQL делает обход таблицы в случае с "<= AND =>" (ps: с BETWEEN тот же результат)?
Средний
