MySQL Forums
Forum List  »  Performance

Re: optimize IP range join: postgresql is 496 times faster
Posted by: Rick James
Date: December 10, 2008 10:46AM

With a lot of assumptions, this may be quite fast:
* your ranges do not overlap
* you have countries for all addresses
* you have an index on begin_num
   select
     ( select r.id_country from range r
       where a.address >= r.begin_num
       order by r.begin_num
       limit 1
     ) as id_country
   from address a

Postgres seems to have some "range" optimizations that MySQL does not have.

Please use SHOW CREATE TABLE xxx; instead of DESCRIBE xxx; -- the former is clearer on indexes.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.