1

I have a table Person with columns personId, teamId, departmentId, among others. Most of the query use a combination of these columns on the where.

Example

Select * from .. where personId = 2 and departementId = 1
Select * from .. where personId = 2 and teamId = 1   
Select * from .. where departmentId = 2 and teamId = 1

My question is, should I create an index for each of these column individually?

Andriy M
  • 76,112
  • 17
  • 94
  • 154
roncansan
  • 2,310
  • 6
  • 27
  • 34

3 Answers3

2

The quick answer is yes - just add an index for each column. Its not likely to be the most optimal solution but in most cases it won't be that far off and it probably won't cause any harm unless you already have many indexes on that table.

The only slightly longer answer is that you should test your query against representative data - The SQL Server Database Engine Tuning Advisor can suggest indexes for you, but only you can check to make sure that these indexes are suitable for other all queries (including inserts / updates) - you need to balance the performance of reads against the cost of maintaining those indexes when writing to the database (as well as any storage / space constraints).

Justin
  • 84,773
  • 49
  • 224
  • 367
2

Either one per column: SQL Server will use Index Intersection

Or, try something like this: three composite indexes. The first column of each is useful as a "single column index" too.

  • departmentId, teamId, personId
  • personId, departmentId, teamId
  • teamId, personId, departmentId

Notes:

  • WHERE clause order doesn't matter
  • SELECT * is bad

Also, it's a good idea to have foreign key columns indexed and either strategy will work

gbn
  • 422,506
  • 82
  • 585
  • 676
0

I would not, as a rule, create 3 indexes on the variations of field usage, but that is just a general rule.

As far as the "how do I do this as a newbie" answer, I would create a workload and use the tuning advisor. It is not an end all solution, and as someone learns more, they get beyond the wizard, but it is a good place to start with. Make sure you have a decent representative sample, as indexes can destroy performance on other queries, if done incorrectly.

Gregory A Beamer
  • 16,870
  • 3
  • 25
  • 32
  • 3
    "indexes can destroy performance on other queries" - bit of a sweeping statement. Yes, it can impact INSERTS, but even inserts need to find the insertion point.... – Mitch Wheat Aug 04 '11 at 14:36
  • @Mitch Wheat: +1 Every DML is a read, whether it's to find the row, check uniqueness, check an FK. I don't really subscribe to the "too many indexes" theory – gbn Aug 04 '11 at 14:38
  • @gbn - you can have an issue when you have a lot of overlap, though. If you update a field that's in 3 different indexes you start multiplying the negative effects. – JNK Aug 04 '11 at 15:07
  • @Mitch: I guess the summary here is think through you indexes rather than just implement. I would disagree on "too many indexes", although I would agree you cannot pick number X as too many. – Gregory A Beamer Aug 04 '11 at 21:03