Do you need select * ? If not, a nonclustered index on the columns in the
select list would cover the query and may result in a seek. It basically
looks like you are getting a bunch of rows too so SQL is going to scan to
leverage sequential IO. If you have to get this much data, you might have to
go with partitioned view and\or table to make the scan smaller. How rows of
the 5 million does that query return?

--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio


"robpoz" <robpoz@newsgroup.nospam> wrote in message
news:715A2B29-68FB-43C4-8B11-29D040CD4605@microsoft.com...
> 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:
>
>