CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Padhu
Padhu

Reputation: 107

What type of index should i create?

Table has 6 columns. column1 is unique, so I created the primary key on column1 and created non-clustered index for all columns:

         create primary key for column1
         create index for col2, col3, col4, col5, col6, col1

So I have any column in where clause will use the index except column1 after that it takes missed column1 in primary key and will integrate both result . because of this it will increase the performance right ?

Upvotes: 1

Views: 107

Answers (2)

vnov
vnov

Reputation: 335

If you optimize only those queries and you dont care about the size of indexes, then yes create them exacly like that - (col1, col2), (col3, col4) and (col1, col4). Note, that the order of columns here is unimportant.

But if you have lots of data, or you modify your data often (insert, update of the keys or delete), than you also should take that into acount. Rearanging of index takes time as well and the more columns, the more rearanging (and less options for the sql server). Indexes also takes space, so if you have big data (and limited edition or disk), than you might need to optimize space as well.

You could then choose a trade-off solution and create just two indexes (col1) and (col4) which would partly cover all three queries. It all depends on the selectivity of col1 and col4 and how often you call the queries and how fast you need them to be.

To understand the basics, see the best guide to indexes out there.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

A compound index made up from multiple columns can only ever be used if the n left-most columns in that index are being used in a where clause.

So if you have an index on (col1, col2, col3, col4), that index might be used

  • when your query has WHERE conditions on all four columns
  • when your query has WHERE conditions on col1, col2, col3
  • when your query has WHERE conditions on col1, col2
  • when your query has WHERE conditions on col1

but it CANNOT ever help you if your query has where conditions on e.g. col3, col4 (because those are NOT the 2 left-most columns in the index)

So in your case, you would need an index with

  • col1, col2
  • col3, col4
  • col1, col4

There's no single index that can satisfy these needs - if you e.g. had an index on col1, col2, col4, that would likely be used for query #1, but it cannot really be used for query #3 (or only to handle the col1 in the beginning - but it won't help with col4).

So in your case, the only option is to have three separate indexes - one for each type of query - and see if these really help to speed up your queries. This however also means there are three indexes that need to be updated and maintained, whenever the table gets insert, update or delete operations. Try and measure and then decide if the speed increase warrants possible additional overhead on insert, update and delete operations.

Upvotes: 3

Related Questions