| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-04-29 : 21:04:41
|
| Say I've got a tableUsers-----UserID (int, identity, Primary key, Clustered)IsHappy (tinyint)xyz...for any given user, IsHappy will be either 0 or 1 (yes, I know about bits but there's a good reason for this).Now, my primary key is userID, of course. But I've got a slow-running query that does something like "select UserID,x,y,z from users where isHappy=1".Right now, in addition to that clustered primary key, I have a nonclustered index on IsHappy, which may be a waste, as there are only two possible values. Out of about 300,000 rows, maybe 5,000 have IsHappy set to 1, the rest are 0. (Yeah, not a cheery place).Can/should I make my primary key a covering index on UserID and IsHappy even though the only real unique value is UserID? I see that it would increase the size of my clustered index references from 4 bytes to 7, which would affect about 20 foreign key relationships to users on UserID.Thanks for any insight-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-04-30 : 20:36:05
|
| Nobody has any ideas on this? I've found a few more situations like this in my DB, and would love to hear if anyone has experimented or knows offhand why this is a good/bad idea.Thanks-b |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-04-30 : 20:45:50
|
quote:
Can/should I make my primary key a covering index on UserID and IsHappy even though the only real unique value is UserID? I see that it would increase the size of my clustered index references from 4 bytes to 7, which would affect about 20 foreign key relationships to users on UserID.
No, you should not..What is the problem anyway? Is the query taking too long, table scans etc...DavidMTomorrow is the same day as Today was the day before. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-04-30 : 21:41:40
|
| Like my good friend David above, I'd like to hear what your definition of "slow running query" is. I have almost identical scenarios which perform very respectably...more info pls--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-05-01 : 13:43:48
|
| My issue is that the table in question sees a *lot* of inserts, so I'm trying to minimize the number of indexes on it. I was hoping to get away with using my primary key as a covering index rather than creating a nonclustered index on a column that only has two values, which BOL says does limited good and is a bad practice in general.Cheers-b |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-05-01 : 13:57:23
|
| Since your table is subject to a lot of inserts, get rid of the non-clustered index and make the clustered primary key a covering index. For a table subject to many inserts, it is good to have a clustered index.*************************Just trying to get things done |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-01 : 14:13:31
|
| I agree with royv on the fact that nonclustered index should be dropped -- it simply isn't selective enough, so it will never be used. However, I don't think that adding IsHappy to the primary key would do any good if you typically run queries with only isHappy as a WHERE criteria. Here is my reasoning: If you add isHappy as the last key in the clustered index, the WHERE clause would still have to scan the entire index for your record (kinda like searching a phonebook for some first name)...If you add isHappy as the first key to the clustered index, it would probably speed up this specific query... But due to the low selectivity of this field, I suspect the foreign key lookups may slow down (and you mentioned there are 20 foreign keys referencing this table!)So to summarize: loose the non-clustered index and don't do anything else.---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested.Edited by - izaltsman on 05/01/2002 14:17:26 |
|
|
|
Webern
Starting Member
1 Post |
Posted - 2013-05-30 : 18:00:00
|
| I recommendCREATE INDEX IX_YourIndexNamingConventionON thistbl( isHappy,UserID)INCLUDE( x,y,z,...) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-31 : 00:31:54
|
Wow! Possibly a record here!Necro'd an 11 year old threadNice work. Welcome to the forums Webern. Might want to try reading some questions that were posted this decade  |
|
|
|
|
Covered index on primary key?