Forum Moderators: open

Message Too Old, No Replies

How do i create an index for a query on joined tables

         

artie2004

10:44 pm on Jul 16, 2006 (gmt 0)

10+ Year Member



Hi. I have the following table:

CREATE TABLE Jobseekers
(
jobseeker_id int
LastName varchar,
FirstName varchar,
Address varchar,
Age int
)

CREATE TABLE Resumes
(
resume_id int,
jobseeker_id int,
Title varchar,
Objective varchar,
Education varchar,
Experience varchar
)

And the following query for example:

"Select From Jobseekers, Resumes WHERE Jobseekers.jobseeker_id=Resumes.jobseeker_id AND Resumes.Educations='Bachelors' AND Resumes.Experience='3 Years'"

I know how to create an index on a single table query. Do i need to create a different kind of index for a query on multiple table query? Thanks.

zCat

11:05 pm on Jul 16, 2006 (gmt 0)

10+ Year Member



As long as both "Jobseekers.jobseeker_id" and "Resumes.jobseeker_id" are indexed via their respective tables, your database's planner should use them appropriately.

AFAIK there's no such think as a multiple table index.

aspdaddy

6:27 pm on Jul 17, 2006 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



If you are using SQL you might want to use a view, or even an indexed view. Whether you can index a view depends on several factors, including sql version,installation,and query design. Some info in the link below.

Indexed Views in SQL Server 2000 [sqlteam.com]