We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

how to determine which columns to INDEX?

royjayd
royjayd asked
on
Medium Priority
460 Views
Last Modified: 2012-05-11
hi guys

I am using SQL server 2005
I have three tables, trying to figure which columns should i index. The tables are

Project
projectID
Name
Status

Users
UserId
Fname
Lname
email

Project_Users
ProjectId
UserId
Role


The user searches in the Project table by ProjectId
The User searches in the Project_Users table by UserId.
The User searches in the Users table by UserId, Fname or Lname.

In your opinion which coulms in tables should be indexes?

Any ideas appreciated.

thanks
Comment
Watch Question

Commented:
If the database has been in production and statistics have been generated, try using the built-in index tuning wizard.
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
These are must

Project.projectID (PK)
Users.UserId (PK)

Project_Users : ProjectId, UserId (PK) gives answer for find role for project pand user u. Also what users in project p

Optional

Users : UserId, ProjectId (AK) gives answer for find projects for user u



HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
oops, I deleted some wording I guess, last line above should read as

Optional

Project_Users : UserId, ProjectId (AK) gives answer for find projects for user u

Author

Commented:
are there any downgrades to assigning indexes? If i assign an index which i shouldnt be assigning will there be a perfomance downgrade?
thx
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
yes there would be... but negligeble... when updating, deleting, inserting a bit more work will be needed on db side... rearrange the indexes... more indexes more work... but if tables ara small dont worry... if you have millons of records with lots of multiple columns indexes, then you should worry...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
Final review on thos post, and my answer is:

The user searches in the Project table by ProjectId
Project.projectID (PK)

The User searches in the Users table by UserId, Fname or Lname.
Users.UserId (PK)
Users.FName (IX)
Users.LName (IX)

The User searches in the Project_Users table by UserId.
Project_Users : UserId, ProjectId (PK) gives answer for "find projects for user u"
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
HainKurt:, question for you.

>>>The user searches in the Project table by ProjectId
Project.projectID (PK)

shouldnt projectID  in this table be set as index?

thx
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
PK is already an index, just a special one (which physically sorts the records and it will be unique)
Each table should have one PK...

Author

Commented:
Also I think if a column contains duplicate values
It cannot be indexed, right?
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2011

Commented:
- if you are a DBA, index maintenance is another aspect to look into. i'm sharing an article that covers what you need to maintain the existing index if it helps:
http://www.mssqltips.com/tip.asp?tip=1339

your question on "are there any downgrades to assigning indexes? If i assign an index which i shouldnt be assigning will there be a perfomance downgrade?"

- in that same article, i would suggest you to look into topic 4. Over indexed tables and 5. Under indexed tables which covers on too many indexes on a table may degrade performance and too few will results in a full table scan which will also degrade performance. these tips are good when planning to create index for your tables.

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if you create a PK on some columns, they cannot be duplicated

for example users table with userid as pk
you cannot insert 2 records with same userid

for example user_projects table with userid, projectid as pk
you cannot insert 2 records with same userid & projectid

UserID  PrjID
1          101
1          102
2          102
3          105
3          102

these are valid, but you cannot enter (1,102) again since it is already there and there is a PK on these columns
but you can enter (1,103)

Author

Commented:
guys , i understand all those concepts.
>>>if a column contains duplicate value, it can be index BUT you cannot create a PK on that column

i am specifically talking about table Project_Users where
i have composite primary key (ProjectId +UserId).
CREATE UNIQUE INDEX IDX_UQ_USER_ID ON PROJECT_USERS (USER_ID);  works
but
CREATE UNIQUE INDEX IDX_UQ_PROJECT_ID ON PROJECT_USERS (PROJECT_ID); fails

Error is
18:00:50  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 1505, SQL State: S0001]  The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'etrading.PROJECT_USERS' and the index name 'IDX_UQ_PROJECT_ID'. The duplicate key value is (227565).
which is why i said columns with duplicate values cannot be index ed( the above error says that,right?)




Top Expert 2011

Commented:
- yes it fail because you are specifying UNIQUE index where there are duplicate data in that column. take out the unique:

CREATE INDEX IDX_UQ_PROJECT_ID ON PROJECT_USERS (PROJECT_ID);

Author

Commented:
aaah.. Makes sense.
Thx
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.