1

I am adding two fields, facilityCode and facilitySubCode, to an existing table. 

ALTER TABLE SampleTable ADD facilityCode int not null
ALTER TABLE SampleTable ADD facilitySubCode int null

The application will search for records from this table using either facilityCode or facilitySubCode but not both. I want to add an index to help with this query, but I'm not sure whether to create:

1) A single index with both columns. If so, which column to list first?

2) 2 indices, each consisting of 1 column.

As info, a facilityCode can have 0 to many sub codes, and a subcode has just 1 facility code. As far as usage statistics, I do not know which search will be more popular, searching by facility or searching by sub code. We can assume that MOST records will have both a facilityCode and a facilitySubCode.

Which option should I choose and why?

Bill Gregg
  • 7,067
  • 2
  • 22
  • 39

1 Answers1

2

If you want to search by either facilityCode, or facilitySubCode, but never both columns at once, you will need 2 indices, one for each column.

The reason why one single index across both columns wouldn't be ideal would be because whatever column wasn't on the leading edge of the index would not be able to be searched using that index by just that column.

For more details as to why this is the case, I would suggest reading the answers to this question.

Community
  • 1
  • 1
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109