0

Let's assume we have a table with name Tournament and columns of this table are Id, Name, GameId, TypeId, cityId, Status, Format. Except Id and Name columns I want to search this table with all of the combinations of these columns, I mean I can search with GameId & Status or TypeId & CityId & Status or Status & Format.

As you can see all of the search options count is equal to 

=C(5,1)+C(5,2)+C(5,3)+C(5,4)+C(5,5).

So here is my question,what is the best indexing scenario and why? I think the options are,

  1. Index for each column.
  2. Composite ındex for all columns.

I know composite index is really faster than single index but if you go correct order, I mean if I have a composite index on [GameId, TypeId, CityId, Status, Format] columns and search like GameId = 1 & TypeId = 2 & CityId = 3 & Status = 5 & Format = 6 it will work great, but how about if I search GameId = 1 & Format = 6 I can not realize how this query will works fast enough, as I know SQL Server composite index is just a b-tree and the b-tree keys of composite index are ordered from left to right columns.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
spartacus
  • 544
  • 2
  • 8
  • 16
  • what do you intend to use as the clustered index? – Paul Maxwell Sep 30 '14 at 13:51
  • If you need to search by every possible combination of values - you either need to have one index for each possible combination of columns - or you just skip indexes at all, since they most likely won't be used anyway....... seriously: scenarios like these are rarely handled well. Try to identify the **most common** queries - those top 3 queries that make up 80% of your searches - and then index **those** to speed up the most common queries - and just forget the rest – marc_s Sep 30 '14 at 14:47
  • i think u are absolutely right marc_s, thanks for ur reply. – spartacus Oct 01 '14 at 11:10

0 Answers0