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

Question

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

• person_id doesn't have an INDEX
• school_id doesn't have an INDEX
• {person_id, school_id} has an INDEX

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

Answer 1

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.

Answer 2

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.