When I suggested adding a nonclustered index on iplow, iphigh, I meant you
could try adding that nonclustered index in addition to your current
clustered index, not in replace of it. But that would probably only be
ful if your rows are wide (in the sense of average number of bytes per
row, not the number of columns).

However, in a different message in this thread, you stated that you knew
there could only be one match. Is this because you can't have overlapping
ip ranges in your table? If that's true, you could try writing your queries
to take advantage of that fact. For example, replace

select * from ip2location where iplow > 12345 and iphigh < 678910

with

select <column list>
from ip2location p
where p.iplow = (select min(iplow) from ip2location p2 where p2.iplow >
12345)
and p.iphigh < 678910

and replace

select * from ip2location where 1288141274 between iplow and iphigh

with

select <column list>
from ip2location p
where p.iplow = (select max(iplow) from ip2location p2 where p2.iplow <=
1288141274)
and p.iphigh >= 1288141274

Tom


"robpoz" <robpoz@newsgroup.nospam> wrote in message
news:BC61A958-D139-4F42-B3A3-A2273B4D88E7@microsoft.com...
> Tom, Jason, Charles,
>
> Thanks very much for your response.
>
> Sorry, I simplfied the select statement to minimize typing while posting
> my
> question. Our select statement is not select * but only selecting one
> varchar
> field. Also, the rows are not wide at all - maybe 5-6 columns.
>
> Is it better to have a nonclustered index instead of clustered? I thought
> clustered indexes were better for range searches and the table contains a
> unique clustered index containing two columns.
>
> I'll try the suggestions you've pointed out.
>
> Thanks.
>
> Rob
>
> "robpoz" wrote:
>
>> We're designing an application where we need to determine if a specified
>> ip
>> address (represented as a bigint) is between a iplow and iphigh column
>> we've
>> got in a sql/server table.
>>
>> Currently I've got iplow and iphigh columns defined as bigint. I've also
>> created a clustered, unique index containing both iplow and iphigh
>> columns.
>> We've got slightly over 5 million rows in our table.
>>
>> How can we ideally design the table/indexes to make these queries execute
>> as
>> quickly as possible. Queries such as:
>>
>> select * from ip2location where iplow > 12345 and iphigh < 678910
>>
>> these queries are also slow.
>>
>> Thanks.
>>
>> Rob
>>
>>
>> select * from ip2location where 1288141274 between iplow
>> and
>> iphigh
>>
>> can take up to 20 seconds to return 1 row!
>>
>> I've also tried range based queries ie:
>>
>>