SOLVED

Composite Non clustered Index-Creation

Copper Contributor

HI Team,

I have two different queries and I need to create index for them .

 

1)One query will have these where conditions with column "bundle_id,resource_id,unique_Id"

2)Another query will have where conditions like  "bundle_id,unique_id".

 

Should I need to create two different index with one "bundle_id,resource_id,unique_Id" and another "bundle_id,unique_id"?

 

or one index with "bundle_id,resource_id,unique_Id" is enough ?

 

Please enlighten me .

4 Replies
Hi,
It would be better for creating an Index for "bundle_id,unique_id" columns.

@Arshad440 

HI Arsh,

 

Is that mean that one index will optimize both queries ?But resource_id column is ignored right?

 

I already have seven index in that table .Now my concern is why we need two index? More index is also a overhead right?

If we definitely need two index then I can convince myself  and go with it .

 

Please advise 

best response confirmed by SenthilMadhan (Copper Contributor)
Solution

@SenthilMadhan 

 

Hi, Senthil.

 

The following answer from Dan is the best and most concise I've read:

 

 

In short, you only need the one index to cover both queries. However, query optimisation is not as clear-cut.

 

And to answer the other part of your question: yes, more indexes represent more overhead. Whether the overhead is justified depends on the frequency of the query and the frequency of the updates to those columns. If you query often and update infrequently, then it's probably worth creating the single index containing all three columns.

 

We can't tell from your question which columns are more unique than others, but if you structure your index from left to right in order of least unique to most unique, you will likely end up with a decently-optimised query - based on your two example scenarios.

 

For example, if:

 

  • bundle_id is less unique than resource_id; and
  • resource_id is less unique than unique_id.

 

Then you'd create your index in the order of (logical syntax only):

 

  • CREATE INDEX (bundle_id, resource_id, unique_id).

 

And then in your query, you'd reference (again, logical syntax only):

 

  • WHERE bundle_id = something AND resource_id = something AND unique_id = something.

 

So, the WHERE matches the left-most order of the index as closely as possible.

 

But this is just a simple explanation on optimisation. Reads Dan's reply and have a good hard think about which columns really belong in the index and in what order they should be placed.

 

I do have to wonder though about unique_id. If that's truly unique across the table then that undermines everything about this question. But only you know how the tables and queries are being used, so I only mention it as a point of curiosity.

 

Cheers,

Lain

Thank you Lain
1 best response

Accepted Solutions
best response confirmed by SenthilMadhan (Copper Contributor)
Solution

@SenthilMadhan 

 

Hi, Senthil.

 

The following answer from Dan is the best and most concise I've read:

 

 

In short, you only need the one index to cover both queries. However, query optimisation is not as clear-cut.

 

And to answer the other part of your question: yes, more indexes represent more overhead. Whether the overhead is justified depends on the frequency of the query and the frequency of the updates to those columns. If you query often and update infrequently, then it's probably worth creating the single index containing all three columns.

 

We can't tell from your question which columns are more unique than others, but if you structure your index from left to right in order of least unique to most unique, you will likely end up with a decently-optimised query - based on your two example scenarios.

 

For example, if:

 

  • bundle_id is less unique than resource_id; and
  • resource_id is less unique than unique_id.

 

Then you'd create your index in the order of (logical syntax only):

 

  • CREATE INDEX (bundle_id, resource_id, unique_id).

 

And then in your query, you'd reference (again, logical syntax only):

 

  • WHERE bundle_id = something AND resource_id = something AND unique_id = something.

 

So, the WHERE matches the left-most order of the index as closely as possible.

 

But this is just a simple explanation on optimisation. Reads Dan's reply and have a good hard think about which columns really belong in the index and in what order they should be placed.

 

I do have to wonder though about unique_id. If that's truly unique across the table then that undermines everything about this question. But only you know how the tables and queries are being used, so I only mention it as a point of curiosity.

 

Cheers,

Lain

View solution in original post