# optimize IP range join: postgresql is 496 times faster **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 22, 2008, 8:51pm UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938 "2008-09-22T20:51:29Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![nluv4hs](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@nluv4hs](https://forums.percona.com/u/nluv4hs)\ **Post date:** [September 22, 2008, 8:51pm UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/1 "2008-09-22T20:51:29Z")
I am very curious to know how to write the following join query for MySQL so it will perform as well as PostgreSQL. I never used PostgreSQL before now. I like MySQL and I always use it. However I was disappointed by its performance on this query and I installed PostgreSQL to compare. The purpose of this query is to map ip addresses to countries. On my CentOS 5 machine, MySQL 5.0.45 takes 10 mins 45 seconds, PostgreSQL 8.1.11 takes 0 mins 1.3 seconds. Yes MySQL is 496 times slower. I’m sure there must be a way to hint MySQL to run faster. Here is the query: mysql\> select range.id\_country from address join range on address.address between range.begin\_num and range.end\_num;mysql\> describe address;±--------±-----------------±-----±----±--------±------+| Field | Type | Null | Key | Default | Extra |±--------±-----------------±-----±----±--------±------+| address | int(10) unsigned | YES | | NULL | |±--------±-----------------±-----±----±--------±------+mysql\> describe range;±------------±--------------------±-----±----±--------±------+| Field | Type | Null | Key | Default | Extra |±------------±--------------------±-----±----±--------±------+| begin\_num | int(10) unsigned | NO | PRI | | || end\_num | int(10) unsigned | YES | UNI | NULL | || id\_country | tinyint(3) unsigned | YES | MUL | NULL | |±------------±--------------------±-----±----±--------±------+ Both tables are MyISAM type. Table `address` is 2124 rows (all distinct). Table `range` is 105920 rows (all distinct). The best answer I found so far is here. Steinbrink gives a way to write it as a join on subquery that my MySQL finished in 6 min 42 sec (63% the time of the simple join version). That is still 310 times slower than PostgreSQL. Too slow! Actually there is a small error in the SQL at that url: ORDER BY ip\_address DESC should be: ORDER BY start DESC Here is MySQL’s explanation of the original query: mysql\> explain select range.id\_country from address join range on address.address between range.begin\_num and range.end\_num;±—±------------±--------±-----±----------------±-----±--------±-----±-------±-----------------------------------------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±-----±----------------±-----±--------±-----±-------±-----------------------------------------------+| 1 | SIMPLE | address | ALL | NULL | NULL | NULL | NULL | 2124 | || 1 | SIMPLE | range | ALL | PRIMARY,end\_num | NULL | NULL | NULL | 105920 | Range checked for each record (index map: 0x7) |±—±------------±--------±-----±----------------±-----±--------±-----±-------±-----------------------------------------------+ Here is PostgreSQL’s explanation of the original query: postgresql# explain select range.id\_country from address join range on address.address between range.begin\_num and range.end\_num; QUERY PLAN----------------------------------------------------------------------------------------------------------------- Nested Loop (cost=5.72…7061709.90 rows=83990942 width=2) → Seq Scan on range (cost=0.00…3316.47 rows=185547 width=18) → Bitmap Heap Scan on address (cost=5.72…31.25 rows=453 width=8) Recheck Cond: ((address.address \>= “outer”.begin\_num) AND (address.address \<= “outer”.end\_num)) → Bitmap Index Scan on addresses\_pkey (cost=0.00…5.72 rows=453 width=0) Index Cond: ((address.address \>= “outer”.begin\_num) AND (address.address \<= “outer”.end\_num)) Here is MySQL’s explanation showing that a simple query can use the index on begin\_num in a “range” type query plan: mysql\> explain select id\_country from range where 123123123 between begin\_num and end\_num;±—±------------±--------±------±----------------±--------±--------±-----±-----±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±--------±------±----------------±--------±--------±-----±-----±------------+| 1 | SIMPLE | range | range | PRIMARY,end\_num | PRIMARY | 4 | NULL | 35 | Using where |±—±------------±--------±------±----------------±--------±--------±-----±-----±------------+ Others encountered this problem before. References: [LIST] [_] MySQL Forums :: Optimizer :: How to optimize a range (best answer I found but not good enough) [_] MySQL Performance Blog: Optimize IP Range Join / Daniweb: Optimize IP Range Join - MySQL [_] MySQL Performance Blog: Range Optimization [_] MySQL :: inner join on range criteria: unable to use index? [_] Sergey Petrunia’s blog Use of join buffer is now visible in EXPLAIN [_] MySQL :: MySQL 5.0 Reference Manual :: 7.2.5 Range Optimization [_] MySQL Bugs: #26963: Incorrect query results with CONST join compared to RANGE or ALL [_] MySQL Bugs: #9693: bug in trying to join a value on a range (using indexes) identical post on mysql performance forum [/LIST] ---
**Author:** ![shantanuo](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/shantanuo/32/813_2.png) [@shantanuo](https://forums.percona.com/u/shantanuo)\ **Post date:** [July 17, 2009, 10:40pm UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/2 "2009-07-17T22:40:50Z")
Hi, You can try \> and \< instead of between. ON address.address \> ranges.begin\_num AND address \< ranges.end\_num ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 20, 2009, 5:50am UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/3 "2009-07-20T05:50:55Z")
MySQL chooses the wrong join order. Try this: SELECT range.id\_country FROM range STRAIGHT\_JOIN address ON (address.address between range.begin\_num and range.end\_num); Note that the time this query takes, depends on the number of ranges and not so much on the number of addresses. The range scan on an index that you give is nice, but not very useful. It is simply not selective enough. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 20, 2009, 5:51am UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/4 "2009-07-20T05:51:55Z")
Old thread alert ) ---
**Author:** ![nluv4hs](https://avatars.discourse-cdn.com/v4/letter/n/0ea827/32.png) [@nluv4hs](https://forums.percona.com/u/nluv4hs)\ **Post date:** [July 29, 2009, 5:45pm UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/5 "2009-07-29T17:45:29Z")
Thanks for the replies. I’ll try it. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 30, 2009, 4:53am UTC](https://forums.percona.com/t/optimize-ip-range-join-postgresql-is-496-times-faster/938/6 "2009-07-30T04:53:35Z")
Let me know your findings.