I have a table that stores IP address ranges for a city and there are millions of records in this table. I'm sure that many of you that deal with IP addresses have a similar table to me (I've simplified my table in this example):
CREATE TABLE [dbo].[IPRangeByCity] ( [ID] [int] IDENTITY(1,1) NOT NULL, [IPIntegerStart] [bigint] NOT NULL, [IPIntegerEnd] [bigint] NOT NULL, [Country] [nvarchar](150) NOT NULL, [City] [nvarchar](150) NULL CONSTRAINT [pk_IPRangeByCity] PRIMARY KEY CLUSTERED([ID] ASC), ) ON [PRIMARY] GO
Now I don't save, update, or delete any records from this table. I only read from this table. When I read from this table, I take an IPv4 address, convert it to its integer form, and using the integer form of the IPv4 address, I lookup the city between the IP address range for this integer.
For example, let's say the IPv4 address is "22.214.171.124".
"126.96.36.199" converts to the integer 3153453940. Then I run the following select statement to find the city that is associated with this IP address:
select * from IPRangeByCity where 3153453940 between IPIntegerStart and IPIntegerEnd
My question is, if I only ever read from this table with the select statement above, how should I structure my index to improve the lookup time of the select statement?
Off the top of my head, if I set the index for this table to the column "IPIntegerStart", it seems like it may be a good index for my select statement. For example:
CONSTRAINT [pk_IPRangeByCity] PRIMARY KEY CLUSTERED([IPIntegerStart] ASC)
However, I'm not really sure. Does anybody know what would be the best index to set my for my table, given my select statement? Should it be a clustered or non-clustered index? Should it be a multi column index (ie. an index with both the columns IPIntegerStart and IPIntegerEnd)? Any help would be appreciated. Thanks.