Why is doing a top(1) on an indexed column in SQL Server slow?

Question

I'm puzzled by the following. I have a DB with around 10 million rows, and (among other indices) on 1 column (campaignid_int) is an index.

Now I have 700k rows where the campaignid is indeed 3835

For all these rows, the connectionid is the same.

I just want to find out this connectionid.

 use messaging_db;
 SELECT     TOP (1) connectionid
 FROM         outgoing_messages WITH (NOLOCK)
 WHERE     (campaignid_int = 3835)

Now this query takes approx 30 seconds to perform!

I (with my small db knowledge) would expect that it would take any of the rows, and return me that connectionid

If I test this same query for a campaign which only has 1 entry, it goes really fast. So the index works.

How would I tackle this and why does this not work?

edit:

estimated execution plan:

select (0%) - top (0%) - clustered index scan (100%)

Answer 1

Due to the statistics, you should explicitly ask the optimizer to use the index you've created instead of the clustered one.

SELECT  TOP (1) connectionid
FROM    outgoing_messages WITH (NOLOCK, index(idx_connectionid))
WHERE  (campaignid_int = 3835)

I hope it will solve the issue.

Regards, Enrique

Answer 2

I recently had the same issue and it's really quite simple to solve (at least in some cases).

If you add an ORDER BY-clause on any or some of the columns that's indexed it should be solved. That solved it for me at least.