| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2011-08-25 : 13:20:19
|
I have a many-to-many table containing ID int IDENTITY(1,1), userID int, sgsID int.There are about 20,000 rows in the table. Aside from the clustered primary key index on ID, I also created a unique index on userID+sgsID so that two identical rows could not be created. I assumed that this index would also be used within queries like this:SELECT userID FROM myTable WHERE sgsID = 1234 Recently however this simple query started to time out. Once my blind panic wore off, I created another index (Non-unique, Non-clustered) on sgsID. The transaction then completed in a split-second.So, my question is, does a unique index not get used for SELECT queries?Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-25 : 13:26:27
|
| whats sgsID? whats the selectivity of that column ie what would number of distinct values in it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-25 : 15:02:35
|
| "I assumed that this index would also be used within queries like this: SELECT userID .. WHERE sgsID=1234"If the index was sgsID, userID I would expect it to be used, but userID, sgsID is the "wrong way round" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-25 : 18:34:31
|
| http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/--Gail ShawSQL Server MVP |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-26 : 03:37:10
|
Hi everyone,Thanks for your responses. Sorry for my slow response, I didn't actually receive any notification emails from this topic thread...quote:
whats sgsID? whats the selectivity of that column ie what would number of distinct values in it?
The table maps users to skills. Because the table is a many-to-many relationship, I run queries using either userID or sgsID, or even both in my WHERE clauses. Because a user should only be mapped to a skill once, I used a unique index across those two columns to reflect this. I thought that SQL would use this index regardless of the query being executed. I know now this is not correct.quote:
If the index was sgsID, userID I would expect it to be used, but userID, sgsID is the "wrong way round"
I find this very interesting. I assume (correct me please if I'm wrong) that I've done the right thing by creating an index on sgsID, to accompany the unique index that has userID as the first column? Also I assume therefore that because userID is the first column in the unique index, that I don't now need to create another index just for the userID column? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-26 : 04:42:40
|
| Yes and yes, please go and read my blog post.--Gail ShawSQL Server MVP |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-26 : 05:16:37
|
| Hi GailThanks for the clarification. I did read your blog post yesterday; it was very interesting. Its good that being so knowledge, you haven't lost the ability to explain things in plain English to us simpletons! I've added your blog to my RSS list and hope that you can put an article online about the Execution Plan after you've delivered it in October...! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-26 : 11:31:30
|
| When I create mapping tables like this, I normally create two unique constraints with the column order reversed to create a covering index for each order.For your table they would be1. userID , sgsID 2. sgsID, userIDCODO ERGO SUM |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-08-26 : 11:54:47
|
quote:
Originally posted by Michael Valentine Jones
When I create mapping tables like this, I normally create two unique constraints with the column order reversed to create a covering index for each order.
Thanks for that. I presume then that SQL can only use a single index to perform a query? Otherwise two indexes containing just each single column would suffice, correct? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-26 : 12:17:56
|
quote:
Originally posted by R
quote:
Originally posted by Michael Valentine Jones
When I create mapping tables like this, I normally create two unique constraints with the column order reversed to create a covering index for each order.
Thanks for that. I presume then that SQL can only use a single index to perform a query? Otherwise two indexes containing just each single column would suffice, correct?
SQL Server can use more than one index, but it has to do more work.The point of the covering index is to make the lookup as fast as possible, because all the data required is in the index and bookmark lookups are not needed. In effect, it becomes like a table with a clustered index in the column order you need.CODO ERGO SUM |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-26 : 18:07:55
|
| For these types of tables - I probably would not include an IDENTITY column either. This table just holds the many to many relationship and you would never reference or use the IDENTITY column.Instead of a unique index on both pairs (as Michael pointed out), I would select one to be the clustered index and the other a unique index - or, for the second one use the non leading column and include the other column.Jeff |
|
|
|
|
Is a Unique Index not used for Select queries?