0

Imagine two indexes:

idx_1 (urlId, name) idx_2 (urlId)

Where urlId is bigint and name is nvarchar(320). And a query which use urlId for filtering and does not affect in any way column name.

  1. Does second index will improve performance? Will the difference be significant?
  2. when single column index used instead of compound multiple column index can boost query performance?

I appreciate answers based on examples.

EDIT:

From the answers I've learned that it isn't so simple. It's not 'black and white'. So I change my question a bit. I assume the second one should be a bit faster because it is smaller. But it seems not in all cases. So WHEN there can be no difference in performance and WHY in these situations it happens.

Improve this question
5
  • 1
    If you never have a WHERE clause that also include name, then idx_1 is pointless and only wastes space and cycles. Commented Sep 10, 2013 at 15:42
  • I agree. But imagine it is neccessary for another queries. But for my purpose it is only important what difference can be in performance of queries that doesn't use name column. Is single column index better or it is irrelevant Commented Sep 10, 2013 at 15:49
  • I would google/bing the phrase "covering index". This is a starter: simple-talk.com/sql/learn-sql-server/… The answers (to your question) are NOT black and white , easy-peezey. Commented Sep 10, 2013 at 15:51
  • 1
    You need to test and see if the performance is better for different queries depending on which index is selected (you can use WITH (INDEX) hint to force it). You also need to test your write workload to see how much more expensive the maintenance becomes relative to how much performance (if any) is gained by the second index for certain queries. I don't think anybody here can tell you whether it is worth having both indexes. Commented Sep 10, 2013 at 15:52
  • 1
    There will be an impact - no question. More data to be stored and managed in the index requires more pages, more I/O operations. But just how much the impact is cannot really be determined/calculated ahead of time - you need to measure, change your index, measure again and compare. There is no formula you can just apply to find out that your performance will be 2.73% less with the second column in the index ..... Commented Sep 10, 2013 at 15:58

1 Answer 1

Reset to default
3

If your data is usually unique based on urlId alone, you are probably better off not creating a secondary index esp. if you can use irlId as the clustered index. Since the 2nd part of the index is much larger than bigint, you will likely have better performance by have more keys per block and you avoid the extra overhead in key maintenance. However, if you can be fairly sure that you don't have lots of records that share a common urlId, the 2-part is likely to be a better choice for better worst-case performance. You really have to measure to be sure though if the difference is important to you.

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

1 Comment

Well I've used some SQL engines (but not MsSQL) for couple years and from this years I've learned basic rule - bigger index (in MB), slower index. But I have a situation (which I can't recreate now) there wasn't difference between these two indexes. I was quite suprised and had one conclusion - It seems I don't understand indexes. That is why I've asked this question - to understand them. So I'll edit my question. To understand I need to know why there can be no difference between them in scope of performance.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.