Query not using index

Hi,

I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l

I have created a clustered index on a,b,d,e which forms the primary
key. I have created a covering index on all the columns of t1. There
are 1 million rows in this table.

My query chooses the TOP20 rows based on some filter conditions. When
I use an "ORDER BY 1", it uses the clustered index and I get the result
in 1 second, whereas it takes around 1minute 48seconds when I use an
"ORDER BY b or any other column". It is not using the covering / the
clustered index.

What is the best way to index this table so that it uses the index and
I get the result within the shortest possible time (just like that of
ORDER BY 1 which take hardly a second).

Thanks..

Sridhar(sridharg.rao@.gmail.com) writes:
> I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l
> I have created a clustered index on a,b,d,e which forms the primary
> key. I have created a covering index on all the columns of t1.

Which in practice is a second clustered index.

> My query chooses the TOP20 rows based on some filter conditions. When
> I use an "ORDER BY 1", it uses the clustered index and I get the result
> in 1 second,

Is that the number 1 or the column l as in "lily"?

> whereas it takes around 1minute 48seconds when I use an
> "ORDER BY b or any other column". It is not using the covering / the
> clustered index.
> What is the best way to index this table so that it uses the index and
> I get the result within the shortest possible time (just like that of
> ORDER BY 1 which take hardly a second).

You will need an index of which the first column is the column in the
ORDER BY clause.

If your queries on this table typically are "TOP 20 ORDER BY any column",
a non-clustered index on each column by a appears to be the best choice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You will need an index of which the first column is the column in the
> ORDER BY clause.

Just to make sure that you understand why this is clear and why an
index on (a, b, c, d, e...) will not work...

If I ask you to find all of the people in a phone book who have a last
name that starts with "D" then you could easily do that. Now, how would
you find all of the people that have a last name where the fourth
letter is "d". You couldn't easily do that. Indexes work pretty much
the same way. If you want to find rows based on column "c" an index on
(a, b, c) isn't going to be nearly as useful as an index on just (c).

HTH,
-Tom.