sql 2008 primary key, index performance
Posted on 2014-08-13
When I build an index that looks exactly like my primary key, select performance increases dramatically. Why is this?
The table has 5 primary key fields, A B C D and LineNum. I want all the records for a given A, B, C and D. My select includes WHERE A= aaa AND B=bbb AND C=ccc AND D=ddd, and has ORDER BY A, B, C, D. The table has 4.1 million records in it. A fill takes about 25 seconds. When I create an index that also has A B C D and LineNum, the fill takes about 2 seconds. An index with just A B C and D takes about 2 seconds. When I delete the index, the query time goes back to about 25 seconds.
Any ideas why adding an index that looks like the primary key speeds up the query?