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

[keith cascio]

Roland,
Thank you! I tried your suggestion but I didn't get much performance improvement. Here's what I did:

mysql> show create table range\G
*************************** 1. row ***************************
       Table: range
Create Table: CREATE TABLE `range` (
  `begin_num` int(10) unsigned NOT NULL,
  `end_num` int(10) unsigned default NULL,
  `id_country` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`begin_num`),
  UNIQUE KEY `end_num_2` (`end_num`),
  KEY `id_country` (`id_country`),
  KEY `begin_num` (`begin_num`,`end_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from range\G
*************************** 1. row ***************************
count(*): 105920
1 row in set (0.00 sec)

mysql> show create table addresses\G
*************************** 1. row ***************************
       Table: addresses
Create Table: CREATE TABLE `addresses` (
  `address` int(10) unsigned default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(*) from addresses\G
*************************** 1. row ***************************
count(*): 2124
1 row in set (0.00 sec)

mysql> select count( r.id_country ) from addresses a join range r on a.address between r.begin_num and r.end_num;
+-----------------------+
| count( r.id_country ) |
+-----------------------+
|                  2120 |
+-----------------------+
1 row in set (10 min 14.21 sec)

mysql> select count( r.id_country ) from addresses a join range r on a.address between r.begin_num and r.end_num;
+-----------------------+
| count( r.id_country ) |
+-----------------------+
|                  2120 |
+-----------------------+
1 row in set (10 min 14.68 sec)

Also with a UNIQUE index:

mysql> drop index `begin_num` on range;
Query OK, 105920 rows affected (6.03 sec)
Records: 105920  Duplicates: 0  Warnings: 0

mysql> alter table range add unique index (begin_num,end_num);
Query OK, 105920 rows affected (11.31 sec)
Records: 105920  Duplicates: 0  Warnings: 0

mysql> show create table range\G
*************************** 1. row ***************************
       Table: range
Create Table: CREATE TABLE `range` (
  `begin_num` int(10) unsigned NOT NULL,
  `end_num` int(10) unsigned default NULL,
  `id_country` tinyint(3) unsigned default NULL,
  PRIMARY KEY  (`begin_num`),
  UNIQUE KEY `end_num_2` (`end_num`),
  UNIQUE KEY `begin_num` (`begin_num`,`end_num`),
  KEY `id_country` (`id_country`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 095958> select count( r.id_country ) from addresses a join range r on a.address between r.begin_num and r.end_num;
+-----------------------+
| count( r.id_country ) |
+-----------------------+
|                  2120 |
+-----------------------+
1 row in set (10 min 10.73 sec)

Keith