Sql-server – Best indexing strategy for query that searches between two bigint columns

clustered-indexindexnonclustered-indexoptimizationsql server

I have the following table in MS-SQL:

CREATE TABLE [dbo].[dbip_locations](
    [ip_from] [bigint] NOT NULL,
    [ip_to] [bigint] NOT NULL,
    [country_code] [nvarchar](64) NOT NULL,
    [region_name] [nvarchar](128) NOT NULL,
    [city_name] [nvarchar](128) NOT NULL,
    [latitude] [float] NOT NULL,
    [longitude] [float] NOT NULL
)

The ip_from and ip_to columns are calculated from ipv4 addresses as such:

return (
      convert(bigint, parsename(@ip, 1)) +
      convert(bigint, parsename(@ip, 2)) * convert(bigint, 256) +
      convert(bigint, parsename(@ip, 3)) * convert(bigint, 65536) +
      convert(bigint, parsename(@ip, 4)) * convert(bigint, 16777216)
    )

I am then using an ipv4 address converted to a bigint using the calculation above to search for the row in which the ip address falls between the ip_from and ip_to columns.

I'll always find one row, while it is not enforced by the schema this is the reality in the data.

Here is the query:

SELECT TOP(1) [latitude], [longitude] FROM [dbo].[dbip_locations] WHERE @ip_int BETWEEN ip_from AND ip_to

I currently have two non-clustered, non-unique indexes on both the ip_from and ip_to columns. The query is executing pretty quickly, but I am performing a huge number of these queries per second and would like to know if I could be getting better performance through the usage of different indexes? Perhaps a clustered multi-column index or by using unique indexes?

Are there any better indexes I could be using?

Best Answer

I think a composite index with both ip_from and ip_to would be the most efficient for this range search. If the only columns returned are latitude and longitude, those queries should be included as non-key columns in the non-clustered for the best performance of this particular query. The index should be clustered instead of you often return the other columns.

Because your query specifies TOP (1) without an ORDER BY, any arbritrary row may be returned within the range search. Specify 'ORDER BY' with columns to uniquely identify a row if you need the same row returned each time.

Related Question