Tengo un problema para encontrar una forma rápida de unirme a las tablas de esa manera:

mysql> explain geo_ip;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ip_start     | varchar(32)      | NO   |     | ""      |       |
| ip_end       | varchar(32)      | NO   |     | ""      |       |
| ip_num_start | int(64) unsigned | NO   | PRI | 0       |       |
| ip_num_end   | int(64) unsigned | NO   |     | 0       |       |
| country_code | varchar(3)       | NO   |     | ""      |       |
| country_name | varchar(64)      | NO   |     | ""      |       |
| ip_poly      | geometry         | NO   | MUL | NULL    |       |
+--------------+------------------+------+-----+---------+-------+


mysql> explain entity_ip;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| entity_id  | int(64) unsigned    | NO   | PRI | NULL    |       |
| ip_1       | tinyint(3) unsigned | NO   |     | NULL    |       |
| ip_2       | tinyint(3) unsigned | NO   |     | NULL    |       |
| ip_3       | tinyint(3) unsigned | NO   |     | NULL    |       |
| ip_4       | tinyint(3) unsigned | NO   |     | NULL    |       |
| ip_num     | int(64) unsigned    | NO   |     | 0       |       |
| ip_poly    | geometry            | NO   | MUL | NULL    |       |
+------------+---------------------+------+-----+---------+-------+

Tenga en cuenta que no estoy interesado en encontrar las filas necesarias engeo_ip por UNA sola dirección IP a la vez, necesito unaentity_ip LEFT JOIN geo_ip (o de manera similar / analógica).

Esto es lo que tengo por ahora (usando polígonos como se recomienda enhttp: //jcole.us/blog/archives/2007/11/24/on-efficiently-geo-referencing-ips-with-maxmind-geoip-and-mysql-gis):

mysql> EXPLAIN SELECT li.*, gi.country_code FROM entity_ip AS li
-> LEFT JOIN geo_ip AS gi ON
-> MBRCONTAINS(gi.`ip_poly`, li.`ip_poly`);

+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | li    | ALL  | NULL          | NULL | NULL    | NULL |   2470 |       |
|  1 | SIMPLE      | gi    | ALL  | ip_poly_index | NULL | NULL    | NULL | 155183 |       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+

mysql> SELECT li.*, gi.country_code FROM entity AS li LEFT JOIN geo_ip AS gi ON MBRCONTAINS(gi.`ip_poly`, li.`ip_poly`) limit  0, 20;
20 rows in set (2.22 sec)

Sin polígonos

mysql> explain SELECT li.*, gi.country_code FROM entity_ip AS li LEFT JOIN geo_ip AS gi ON li.`ip_num` >= gi.`ip_num_start` AND li.`ip_num` <= gi.`ip_num_end` LIMIT 0,20;
+----+-------------+-------+------+---------------------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys             | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | li    | ALL  | NULL                      | NULL | NULL    | NULL |   2470 |       |
|  1 | SIMPLE      | gi    | ALL  | PRIMARY,geo_ip,geo_ip_end | NULL | NULL    | NULL | 155183 |       |
+----+-------------+-------+------+---------------------------+------+---------+------+--------+-------+

mysql> SELECT li.*, gi.country_code FROM entity_ip AS li LEFT JOIN geo_ip AS gi ON li.ip_num BETWEEN gi.ip_num_start AND gi.ip_num_end limit  0, 20;
20 rows in set (2.00 sec)

(En un mayor número de filas en la búsqueda, no hay diferencia)

Actualmente no puedo obtener un rendimiento más rápido de estas consultas, ya que 0.1 segundos por IP es demasiado lento para mí.

¿Hay alguna forma de hacerlo más rápido?