0

I have a table with a nonclustered index1 on ID1 and ID2, in that order.

Select count(distinct(id1)) from table

returns 1

and Select count(distinct(id2)) from table has all the values of the table.

The querys to that table uses ... where id1= XX and id2 = XX

Could it make any performance improvement if I switch the order of the fields of index1 ?

I know it SHOULD be better but maybe: is it indifferent because id1 has only 1 value?

Improve this question
2
  • 1
    Your question is not clear. Your query has id, your index and where clause have id1 and id2. Can you try to express the question a bit better? Sample data and desired results are helpful. Commented Dec 20, 2013 at 14:49
  • Sorry, edited. Now it's correct. Commented Dec 20, 2013 at 15:52

2 Answers 2

Reset to default
1

If I understand correctly, you are comparing these two statements:

where id1= XX and id2 = XX

Under most circumstances, this would use either an index on table(id1, id2) or table(id2, id1). The order of the comparisons in the where (or on) clauses has no impact on which indexes can be used.

Whether you should include a column that has only a single value in the unique index is a different matter. There is a minor performance effect to having a more complex index -- the tree structure has to store more bytes for each key. However, the query:

select count(distinct id2)
from table
where id1 = xx and idx = xx

will actually run faster with a composite index than with a singleton index table(id2). The reason is that the composite index can be used to entirely satisfy the query (in the jargon, it is a "covering index for the query"). The singleton index would need to look up the value of id1 in the table data, which requires extra processing.

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

2 Comments

So this means that in this exact case, Regardless of the order of the index columns, there would be just a small performance improvement between not using a composite index or using a singleton index because all the values of id1 are the same?
@user2920607 . . . Using an index would, in most cases, perform better than not using an index. A singleton would require looking up values in the table data. A composite would cover the query, and not require any additional processing. The composite would probably have slightly better performance.
0

The order you define the columns in your Index matters. If your column ID1 will always only have 1 value, then there is no point in putting it into the index, unless you are using it in a Covering Index in a Non-Clustered Index (meaning an Index not the physical ordering of the Table itself). In general, your first column defined in your Index should be the column with the most Varying Values that you need to search through. Visualize it this way, if you had a table of 1 million rows, and the first Column in your Index only had 1 (or small number) of varying values, then would that Index help you in finding the rows you want among the 1 million? Or would it be better to have ID2 first, which would be more efficient for the search, and which would be more frequently used, is what you have to ask yourself. Below is also more info on your question.

SQL Server Clustered Index - Order of Index Question

If you are using a Non-Clustered index, it may appear to not make a Different if your first Column in your Index is all the same values. However it does matter, the reason being is a Non-Clustered Index is stored on a number of Pages. The more entries you can store on a Page which helps you search faster the better. If you include a Column on a Page which adds no value to the Search, then it will requires the same Index to span more Pages. Meaning more Pages to flip through and Longer Lookups. It also means less Room to add new entries to an Existing Page during Inserts when the index is updated, causing more Page Splits. So there are side effects to the decision to add a Column of only 1 value to the Index. If you are using the Column to "cover" retrieved values in common selects, then you can also use Included Columns in your Index, which has the added benefit of not reordering your Index and yet acts like a Covered Index. If that was the intended purpose originally for adding a Column which only has 1 value.

Improve this answer

3 Comments

" In general, your first column defined in your Index should be the column with the most Varying Values that you need to search through" I knew this, but in this exact case if all the values of id1 are the same and the index is non clustered, i tought that maybe it makes no difference The querys are "update field1 ... where id1= XX and id2 = XX"
It makes a difference, it decreases the room available on each Page of a non-clustered Index. This may be a small difference, depends on your situation. If you are using columns in your index not for searching purposes, but as a "covered index value" then you can use Included Columns in your index instead.
In my case this is not a covered index because the values that are updated are not the one included in the index definition. To sum up, in my case the index works ok but it's using a bit more of space for each page. I'm worried about speed performance and to change the index order won't make any notable difference. Thanks for your help and correct me if i'm wrong

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.