Re: optimize IP range join: postgresql is 496 times faster

[keith cascio]

mysql> analyze table addresses;
+------------------+---------+----------+----------+
| Table            | Op      | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| samiam.addresses | analyze | status   | OK       |
+------------------+---------+----------+----------+
1 row in set (0.02 sec)

mysql> analyze table range;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| samiam.range | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.27 sec)

mysql> explain select count( r.id_country ) from addresses a join range r on a.address between r.begin_num and r.end_num;
+----+-------------+-------+------+-----------------------------+------+---------+------+--------+------------------------------------------------+
| id | select_type | table | type | possible_keys               | key  | key_len | ref  | rows   | Extra                                          |
+----+-------------+-------+------+-----------------------------+------+---------+------+--------+------------------------------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL                        | NULL | NULL    | NULL |   2124 |                                                |
|  1 | SIMPLE      | r     | ALL  | PRIMARY,end_num_2,begin_num | NULL | NULL    | NULL | 105920 | Range checked for each record (index map: 0x7) |
+----+-------------+-------+------+-----------------------------+------+---------+------+--------+------------------------------------------------+
2 rows in set (0.00 sec)