Indexes & "OR"

Hi,
i have 2 tables:
table 1 - called Feed. contains the following fields:
productName
PartNumber
ManufacturerName
PartNumberManufacturerName (this field concatenates the part number field
and the manufacturer name field).
Table 2 - called Products. contains the following fields:
productID
ProductName
PartNumber1
PartNumber2
PartNumber3
ManufacturerName
Mapkey1 - (this field concatenates the PartNumber1 field and the
manufacturername field)
Mapkey2 - (this field concatenates the PartNumber2 field and the
manufacturername field)
Mapkey3 - (this field concatenates the PartNumber3 field and the
manufacturername field)
I have a query that finds the productID for the records in the feed table.
query is as follows
select a.productName, a.partnumber,
from feed a (NOLOCK),
products b (NOLOCK)
where a.partnumberManufacturerName = b.mapKey1 OR
a.partnumberManufacturerName = b.mapKey2 OR
a.partnumberManufacturerName = b.mapKey3
Indexes:
on the feed table, i have an index on the partnumberManufacturerName and on
the products table, i have multiple indexes:
Mapkey1 ,Mapkey2, and Mapkey3
and then on each individual field
Mapkey1
Mapkey2
Mapkey3
My question is which index should i keep and which index should i drop. i'm
not sure which index the query is going to use since i'm using the "OR".
thanks
rafaelwhy the concatentated columns? they don't help performance [and may
hinder it]
why the multiple part numbers in separate columns - is it possible to
have more than 3?
do you have a manufacturers table as well?
i'd seriously consider changing this un-normalized schema to have a
linking table between partnumbers and productIDs (and normalize to have
a manufacturers table)
how does the Feed table get populated?
if you fix the schema, with proper primary and foreign keys, you
probably won't need extra indexes [at least for this query].
Rafael Chemtob wrote:
> Hi,
> i have 2 tables:
> table 1 - called Feed. contains the following fields:
> productName
> PartNumber
> ManufacturerName
> PartNumberManufacturerName (this field concatenates the part number field
> and the manufacturer name field).
> Table 2 - called Products. contains the following fields:
> productID
> ProductName
> PartNumber1
> PartNumber2
> PartNumber3
> ManufacturerName
> Mapkey1 - (this field concatenates the PartNumber1 field and the
> manufacturername field)
> Mapkey2 - (this field concatenates the PartNumber2 field and the
> manufacturername field)
> Mapkey3 - (this field concatenates the PartNumber3 field and the
> manufacturername field)
> I have a query that finds the productID for the records in the feed table.
> query is as follows
> select a.productName, a.partnumber,
> from feed a (NOLOCK),
> products b (NOLOCK)
> where a.partnumberManufacturerName = b.mapKey1 OR
> a.partnumberManufacturerName = b.mapKey2 OR
> a.partnumberManufacturerName = b.mapKey3
> Indexes:
> on the feed table, i have an index on the partnumberManufacturerName and o
n
> the products table, i have multiple indexes:
> Mapkey1 ,Mapkey2, and Mapkey3
> and then on each individual field
> Mapkey1
> Mapkey2
> Mapkey3
> My question is which index should i keep and which index should i drop. i
'm
> not sure which index the query is going to use since i'm using the "OR".
> thanks
> rafael
>
>