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 "187.245.227.116".
"187.245.227.116" 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.