Forum Discussion
Composite Non clustered Index-Creation
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 .
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
- Hi,
It would be better for creating an Index for "bundle_id,unique_id" columns.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
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
