Index Question

[imterpsfan3]

If I have a field in a table that serves as a lookup field to another table, should I index the field in both the main table and the lookup table? Or should I just index the related field in the main table? I did not know if having an index in both tables amounts to double indexing. Thanks in advance...

 

[mrdenny]

If you index both then you will need to be able to store the index twice. You'll have to look at your execution plans to see if there is any benifit. I would assume that thier would be at least some.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Wholsea]

Look at the query you use to do your lookup.

Usually yo do not need to use the index on your "driving" table just the ones you are doing lookups on.

IE:

TableA
---------------
RecordID
First_Name
Last_Name
Zip_code

TableB
---------------
product_id
RecordID


select * from tableA, tableB
where tableA.recordID = tableb.RecordID
and tablea.zip_code = 12345

In this example you might index the recordid on TableB and the Zip_code on tableA



ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise

 

[imterpsfan3]

Wholsea wouldn't RecordID in TableA be the PrimaryKey and therefore already have an index? So in essense you are putting indexes on both tables.

 

[Wholsea]

It depends on how you are working your lookups.

Yeah, you have a primary key, but you are not looking for the primary key, you are looking for the Zip Code (in this example)

If you are doing a full table scan on your diving table anyway, it would do you no good to put an index on the table, being as how you are going to have to bring back all records anyway...

Use the explain plan option to determine where your full table scans are and work on your query that way...

ASP, VB, VBS, Cold Fusion, SQL, DTS, T-SQL, PL-SQL, IBM-MQ, Crystal Reports, Crystal Enterprise