0

After Using the "Display Estimated Execution Plan" on two queries, I ended up creating two indexes as follows:

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table (colA,colB,colC)

and

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table (colA,colD,colE)

I noticed that creating just one index (replacing the above two):

CREATE NONCLUSTERED INDEX IX_ABC ON dbo.my_table (colA,colB,colC,colD,colE)

also optimizes my queries.

So my question is, is it correct that the index with all the columns optimizes as well as two separate ones or is one method preferable to the other.

Thanks

Improve this question

3 Answers 3

Reset to default
2

is it correct that the index with all the columns optimizes as well as two separate ones

No, absolutely not. More likely your query needed an index on (colA) INCLUDE (colB, colC, colD, colE), or perhaps colA is actually a good clustered key candidate. This is a generic case you present and no real conclusion can be drawn. But, as a general rule, indexes on different columns serve different roles and can be used by different queries. An index on (A, B, C) can by no means substitute the need for an index on (B, C), (B, D) or (C, E). Even different order of columns cannot be substituted, an index on (A, B, C) is different and cannot substitute from an index on (B, C, A).

I recommend you go over the topic of Designing Indexes before anything else. After you read it, feel free to ask more questions.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. I know that indexes is an entire subject that can (and should) be studied for days. I will go over the Designing Indexes link you posted.
2

In answer to your question, no the two optimisations are not equivalent.

As for whether one method is preferred above another, it really depends on what your queries are, and how you modify the data.

If you have a query with a specific where clause on (ColA, ColB, ColC) and another on (ColA, ColD, ColE), then the two indexes will serve that scenario well.

However if you are updating the table a lot then you will have the additional overhead of maintaining two indexes.

As with anything like this, you are best to undertake permormance analysis and obtain metrics based on a pattern of expected access.

Improve this answer

Comments

1

To answer your question simply, order is important (think of a multi-level tree) so a combined index will not help with a query that only searches ColA, ColD, and ColE and not ColB and ColC.

So, for example, if you had a query like SELECT colA, colD, colE FROM dbo.my_table WHERE colA = 1 AND colD = 2

the second (combined) index would help somewhat (with the 'ColA' predicate) but the database would still have to scan the remaining results for colD = 2.

On the other hand, the combined index will take up less overall space, and have less of an impact on updates to column A, so you will have to weigh the tradeoffs.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.