CODEWITHSUNDEEP
sqlsql-serverindexing
sports
sports

Reputation: 8147

Compound index and query to a single column: do I need an index?

I have a table SchoolMembers, and it has two columns:

  person_id   school_id
 -------------------------
    1134       361
    1135       362
         ...

The primary key of this table is {person_id, school_id}, so this combination is unique.

The primary key is indexed wih an index called PK_dbo.SchoolMembers

Question: if I have this query:

     SELECT * from SchoolMembers where person_id = 1135

...should I have an INDEX for person_id? I need the previous query to be fast

Upvotes: 4

Views: 767

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Question: if I have this query: SELECT * from SchoolMembers where person_id = 1135 ...should I have an INDEX for person_id?

No. The unique index on {person_id, school_id} is sufficient. In general an index can be used for efficient access by its leading column(s).

This query will start with a BTree seek to the first leaf page containing a row with person_id = 1135. Each leaf page has a forward and backward page pointer to the next and previous page in the index order. The plan will then scan the leaf pages until it has read all the school_id values for that person_id.

Upvotes: 9

Xedni
Xedni

Reputation: 4695

The order of the columns of the primary key make a difference. You've described your key as being by person_id then school_id. With that setup, you should get good performance when searching on person_id. However the opposite is NOT true. If you were to search by school_id, it would not be able to quickly find that row.

Upvotes: 4

Related Questions