4

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?

5

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.

| improve this answer | |
  • I replaced the existing two indexes with a clustered composite index as suggested and I can confirm that it is more performant now. Thanks. – Brian P. Hamachek Dec 6 '16 at 13:20
5

Under the assumptions that the search will always return 1 row (or none), i.e. that the (ip_from, ip_to) intervals do not overlap, the query can become more efficient:

Using a specific ORDER BY and only the ip_from for the search. After we find the first row that matches this condition, we check the second condition as well:

SELECT [latitude], [longitude] 
FROM
    ( SELECT TOP (1) 
          [latitude], [longitude], ip_to 
      FROM [dbo].[dbip_locations] 
      WHERE ip_from <= @ip_int
      ORDER BY ip_from DESC
    ) AS t
WHERE @ip_int <= ip_to ;

A simple index on (ip_from) will be sufficient, the query will do a single index seek and then another read (on the heap or CI) for the relative row from the table.

If you want even better performance, add an index on (ip_from) INCLUDE (ip_to, latitude, longitude).


I should add that the above composite index would help your original query as well. The query would also need an index seek. So, what's the difference?

The - perhaps crucial - difference is that this query will perform far better, even with the simple index, when it is to return 0 results, ie. when you search for an IP that is not in any of the intervals stored in the table. Because it will do the single index seek, find the first matching row and then check the second condition (via the INCLUDE columns or the heap/CI) and reject it.

Your query will have to do an index seek and then traverse the whole rest of the index (so 1/2 of a full index scan on average), only to find out that there are no rows matching the second condition about ip_to.

| improve this answer | |
  • I just tested this and this does perform even better than a clustered composite index. Thanks! – Brian P. Hamachek Dec 6 '16 at 13:24
  • Interestingly, just adding that index was sufficient. My old query and your suggested query had the exact same execution stats. – Brian P. Hamachek Dec 6 '16 at 13:27
  • Yes, because you have the TOP (1). The difference is that this query will perform quite well, even with a simple index on (ip_from). And - perhaps more important - this query will perform far better when it is to return 0 results. Your query will have to do a full table scan or a full index scan, to find out that there are no rows matching. – ypercubeᵀᴹ Dec 6 '16 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.