Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

how to determine which columns to INDEX?

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
0
royjayd
Asked:
royjayd
  • 7
  • 5
  • 3
  • +3
4 Solutions
 
scuthberCommented:
If the database has been in production and statistics have been generated, try using the built-in index tuning wizard.
0
 
lluddenCommented:
Put indexes on what you think will be good columns, and then monitor usage and see if things change.

Indexes aren't just stuck in and forgotten about, they need to be monitored and updated.  That is part of what a DBA should be doing.  There may be other queries later that use different fields, new columns may be added.  One table may be much larger than another.  One table might be small enough that it is faster to cache it in memory.
0
 
HainKurtSr. System AnalystCommented:
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



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
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
0
 
royjaydAuthor 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
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
HainKurtSr. System AnalystCommented:
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"
0
 
Alpesh PatelAssistant ConsultantCommented:
If there is primiry key on Table then It always Cluster Index.

Moreover, create index on those fields which are more used to filter out the result.
0
 
royjaydAuthor 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
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
royjaydAuthor Commented:
Also I think if a column contains duplicate values
It cannot be indexed, right?
0
 
OP_ZaharinCommented:
hi royjayd,
- if a column contains duplicate value, it can be index BUT you cannot create a PK on that column.
- if you need to check for duplicate value on the column you intend to create the PK, i use the Project table for example. the following sql will output list of (1st column) projectID and (2nd column) total duplicate projectID found:

SELECT projectID, count(*) as Duplicate  FROM Project
GROUP BY projectID HAVING count(*) > 1
0
 
OP_ZaharinCommented:
- 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.

0
 
HainKurtSr. System AnalystCommented:
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)
0
 
royjaydAuthor 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?)




0
 
OP_ZaharinCommented:
- 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);
0
 
royjaydAuthor Commented:
aaah.. Makes sense.
Thx
0
 
HainKurtSr. System AnalystCommented:
"
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
"

if you have PK(ProjectID, UserID) then there is no need for IX(ProjectID)
PK(ProjectID, UserID) + IX(UserID) is enough for your needs...

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now