Optimizing IP Range Search? I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.

In my table I have 3 columns 

CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, url NVARCHAR(255))


and I am running this simple query.

SELECT url 
FROM MyTable 
WHERE ipto &lt;= somevalue AND ipfrom &gt;= somevalue


I have also created indexes on all 3 columns clustered on ipFrom and non-clustered on rest of 2. But this query giving me really poor performance in terms of CPU &amp; reads.

Any suggestions.



What i have implemented is i like to redirect user based on the IP address.
I have stored several IP ranges from different areas and states and redirect users on the basis of their IP to appropriate URL.

Yes i think i inserted the data in wrong manner in both columns(and will do column rename later), but point here is to minimize the CPU.

When i look into the execution plan there it converts data in where clause, i dont know why it is converting data in where clause. There is something like this

|--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > Convert([@V])
I'm assuming there is a typo in the question as per Martin's comment.

A problem with a single range seek on either ipFrom or ipTo is that on average it will need to seek half the table. An alternative approach is

1) Add a computed column, on something like: 

 ALTER TABLE MyTable
  ADD granule 
AS CASE WHEN (ipTo-ipFrom)&lt;16 THEN 1 WHEN (ipTo-ipFrom)&lt;256 THEN 2 ELSE 3 END;


2) Index the computed column and ipFrom

CREATE ClUSTERED INDEX IX ON MyTable(granule, ipFrom ); 


3) Query like this:  

SELECT url
FROM   MyTable
WHERE  granule = 1
       AND ipFrom BETWEEN @somevalue - 16 AND @somevalue
       AND ipTo &gt;= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 2
       AND ipFrom BETWEEN @somevalue - 256 AND @somevalue
       AND ipTo &gt;= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 3
       AND ipFrom &lt;= @somevalue
       AND ipTo &gt;= @somevalue;


Precisely how you define the 'granule' function will depend on the spead of ipto-ipfrom in your data.