CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecordip-addresspostgresql-9.3
Emu
Emu

Reputation: 5905

postgres find IP address between 2 db columns

I've a table of IP addresses. The table has two columns names starting_ip and ending_ip. The table looks like the following: enter image description here

Now, let's say I have a random IP address. From that random Ip address, I want to know the city_name. That means I need to know that the random IP address falls between which range, based on starting_ip and ending_ip. Then find 1 record and get the city_name.

I wrote a query something like this:

class IpToCity < ActiveRecord::Base
  establish_connection :"ip_database_#{Rails.env}"
  
  scope :search_within_ip_range, -> (ip_address) do
    self.connection.select_all("
      with candidate as (
        select * from ip_cities
        where ending_ip >= '#{ip_address}'::inet
        order by ending_ip asc
        limit 1
      )
      select * from candidate
      where starting_ip <= '#{ip_address}'::inet;
    ")
  end
end

It's a scope, where I pass the random IP and get a single record. The problem is, the query works fine, but it's very slow. Any suggestion, how to make it faster?

Thanks in advance!

Upvotes: 1

Views: 469

Answers (1)

B Seven
B Seven

Reputation: 45941

Do all the rows match this format?

starting_ip  ending_ip
x.y.z.0      x.y.z.255

If so, then you can add another column for "prefix": x.y.z.

Then match the first 3 octets of the target against the prefix column.

When updating the DB, break rows that span more than one prefix into multiple rows.

The max number of rows is 16.8M (2563), which is small and only slightly bigger than your current 5M.

Upvotes: 2

Related Questions