История изменений
Исправление morkovkin, (текущая версия) :
Похоже проблема в индексах, как и писали выше. НО! Введение индексов может снизить скорость UPDATE и DELETE. Точнее индексы станут бесполезными в базе, которая постоянно обновляется и меняется.
Как быть?
DESCRIBE geoip;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| ip_min | int(11) | NO | | 0 | |
| ip_max | int(11) | NO | | 0 | |
| country | varchar(4) | NO | | | |
| city | varchar(32) | NO | | | |
| isp | varchar(64) | NO | | | |
| proxy | tinyint(1) | NO | | 0 | |
| org | varchar(64) | NO | | | |
...
+--------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
mysql> SHOW INDEX FROM geoip;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| geoip | 0 | PRIMARY | 1 | id | A | 152993 | NULL | NULL | | BTREE | | |
| geoip | 0 | id | 1 | id | A | 152993 | NULL | NULL | | BTREE | | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM geoip;
+----------+
| COUNT(*) |
+----------+
| 152993 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM geoip WHERE 1000000 >= ip_min AND 1000000 <= ip_max;
Empty set (0.16 sec)
mysql> SELECT * FROM geoip WHERE 531939328 >= ip_min AND 531939328 <= ip_max;
+-------+-----------+-----------+
| id | ip_min | ip_max |
+-------+-----------+-----------+
1 row in set (0.19 sec)
mysql> SELECT MAX(ip_max) FROM geoip;
+-------------+
| MAX(ip_max) |
+-------------+
| 2130386431 |
+-------------+
1 row in set (0.08 sec)
mysql> SELECT MIN(ip_min) FROM geoip;
+-------------+
| MIN(ip_min) |
+-------------+
| -2147478272 |
+-------------+
1 row in set (0.09 sec)
Исходная версия morkovkin, :
Похоже проблема в индексах, как и писали выше. НО! Введение индексов может снизить скорость UPDATE и DELETE.
Как быть?
DESCRIBE geoip;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| ip_min | int(11) | NO | | 0 | |
| ip_max | int(11) | NO | | 0 | |
| country | varchar(4) | NO | | | |
| city | varchar(32) | NO | | | |
| isp | varchar(64) | NO | | | |
| proxy | tinyint(1) | NO | | 0 | |
| org | varchar(64) | NO | | | |
...
+--------------+------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
mysql> SHOW INDEX FROM geoip;
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| geoip | 0 | PRIMARY | 1 | id | A | 152993 | NULL | NULL | | BTREE | | |
| geoip | 0 | id | 1 | id | A | 152993 | NULL | NULL | | BTREE | | |
+----------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM geoip;
+----------+
| COUNT(*) |
+----------+
| 152993 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM geoip WHERE 1000000 >= ip_min AND 1000000 <= ip_max;
Empty set (0.16 sec)
mysql> SELECT * FROM geoip WHERE 531939328 >= ip_min AND 531939328 <= ip_max;
+-------+-----------+-----------+
| id | ip_min | ip_max |
+-------+-----------+-----------+
1 row in set (0.19 sec)
mysql> SELECT MAX(ip_max) FROM geoip;
+-------------+
| MAX(ip_max) |
+-------------+
| 2130386431 |
+-------------+
1 row in set (0.08 sec)
mysql> SELECT MIN(ip_min) FROM geoip;
+-------------+
| MIN(ip_min) |
+-------------+
| -2147478272 |
+-------------+
1 row in set (0.09 sec)