Adding An INDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Adding An INDEX

Author

Topic

mary H
Starting Member

32 Posts

Posted - 2008-10-19 : 03:51:07

Adding indexes may help improve the database performance. I'll Index a field if I plan to search or sort against the field frequently.

For example if i want to retrive current orders that have overdue subscribtion payment, I'll add an index to order_id and subscriber_id... Am I on the right track?

Or i should add an index to a field that is used in the WHERE clause.. FOR EXAMPLE (WHERE due_date < NOW())

Can I only add indexes to primary and foreign keys? If i add a foreign key to a field will it improve database performance?

Any help would be appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 04:12:43

Your column selected for index will be based on lots of factors like how often do you search data based on it,how will your select list be, will you be joining on that column etc. Creating an index on column does not guarantee that it will be used by query engine. The usage of index depends on your select list, where condition, the selectivity of data from table,...
if you select on due date often you could consider putting an index on it.
Also there's no restriction that index should be on pk of fk only. you could even have index made up of more than one column if you're sure that you would often use queries including all of them.

mary H
Starting Member

32 Posts

Posted - 2008-10-19 : 04:31:39

Thank you for ur reply,

This is my query:

SELECT t1.* FROM order AS T1 LEFT JOIN
receipt AS t2 USING (order_id, subscriber_id)
WHERE t2.due_date < NOW()
ORDER BY subscriber_id, order_id

For instance, if i always want to look up the overdue payment.Should i add an index to the due_date, order_id and subscriber_id field?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 04:59:25

In that case consider putting a non-clustered index on due_date. Also make a clustered index on joining fields.

mary H
Starting Member

32 Posts

Posted - 2008-10-19 : 06:35:06

Hi visakh,
Should i make a clustered index on subscriber_id and order_id from t1 And a multi-column index on subscriber_id and order_id from t2?

Order_id and subsriber_id in the t1 table are both primary keys and order_id and subscriber_id are not primary keys in the t2 table..

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 06:51:41

yup. you can created a composite clustered index on columns Order_id and subsriber_id if they are your primary key.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-19 : 06:57:57

also see this for some index creation guidelines

http://samsudeenb.blogspot.com/2006/12/sql-server-index-creation-guide-line.html

mary H
Starting Member

32 Posts

Posted - 2008-10-19 : 07:24:50

Thank you that artice was useful