CODEWITHSUNDEEP
sqlsql-serverindexing
Ricky Yip
Ricky Yip

Reputation: 303

Problems with SQL Server table indexing

Subject table:

Id int
DepartmentId int
GroupId int
SchoolId int
Name varchar(255)

Queries

Query #1

SELECT * 
FROM [Subject] t1, [Department] t2 
WHERE t1.DepartmentId = t2.DepartmentId

Query #2

SELECT * 
FROM [Subject] t1, [Group] t2, [Department] t3 
WHERE t1.GroupId = t2.GroupId 
  AND t1.SchoolId = t3.DepartmentId

Query #3

SELECT * 
FROM [Subject] t1, [Group] t2, [School] t3 
WHERE t1.GroupId = t2.GroupId 
  AND t1.SchoolId = t3.SchoolId

Should I create a 3 indexes for table "subject", or just one is enough?

Solution 1

Create one index: departmentId + GroupId + SchoolId

Solution 2

Create three indexes:

  1. DepartmentId for stored proc 1.
  2. DepartmentId + GroupId for stored proc 2.
  3. GroupId + SchoolId for stored proc 3.

Thank you so much!

Upvotes: 2

Views: 69

Answers (1)

marc_s
marc_s

Reputation: 755491

Your solution #1 will not work for query #3 - if you have a compound index on (departmentId, groupId, SchoolId), your query #3 will definitely not be able to use that (since it doesn't use departmentId as a criteria). A compound index only works if you use all n leftmost columns.

What you could do is a mix between your solutions:

Create two indexes:

  • one index on DepartmentId, GroupId which will be useful for queries #1 and #2
  • another index on GroupId, SchoolId for query #3

But be cautious: don't overindex! Too many indices is worse than none at all.

Run your system - observe it's performance. If it's fast enough - be happy, go on a vacation!

Only if it's not fast enough, use performance profiling to find the worst performing queries, and add indices to handle those. Don't just put indices on every query you have - keeping those indices nice and shiny also costs you performance when you insert, update and delete data.

Upvotes: 2

Related Questions