Construct an index for this query

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Construct an index for this query

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-30 : 18:09:07

I want to create an index on Classes for this query. It joins onto another table.

I'm not sure whether to create 3 indexes (InstructorID,ClassroomID,LessonID) each including all of the Classes fields in the select. Or do I only need to include the selected columns for the LessonID index?


SELECT Lessons.LessonID, LessonTitle, Lesson, Lessons.Description, Classes.DateStart, Classes.DateEnd, Classes.DateEnrollmentEnd, Classes.AllowSelfEnroll, RecurType, RecurEndDate, LocationName, Classrooms.Details AS LocationDetails, AdminLogins.ContactFirstName + ' ' + AdminLogins.ContactLastName AS InstructorName 
FROM Classes 
INNER JOIN Lessons ON Lessons.LessonID = Classes.LessonID 
LEFT JOIN AdminLogins ON AdminLogins.LoginID = Classes.InstructorID 
LEFT JOIN Classrooms ON Classrooms.ClassroomID = Classes.ClassroomID 
WHERE Lessons.LessonID = 456

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-06-30 : 20:34:21

What is clustered index in classes Table. Usually you should have index for Foreign Key Columns and I wouldn't put all select Columns as INCLUDE,that will slow down insert/Update big time.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-01 : 01:35:10

What are the existing indexes on the table? (clustered and any other nonclustered)?
How many rows in the table?
How many rows would that query typically return?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources