469,167 Members | 1,154 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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

Jul 23 '05 #1
2 2806
(sr**********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
> 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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by DBNovice | last post: by
13 posts views Thread by Dmitry Tkach | last post: by
6 posts views Thread by Steven D.Arnold | last post: by
2 posts views Thread by Keith C. Perry | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.