Indexes & "OR"

28 Dec 2005 5:48 PM

Rafael Chemtob

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
rafael

28 Dec 2005 5:53 PM

David Portas

Rafael Chemtob wrote:

Show quote

> 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)
>

Of course I can only go by the column names but what is the meaning and
the justification for the PartNumber 1 - 3 and MapKey 1 - 3 columns?
This just screams "Denormalization! Mistake!". If I'm right then you
may not have to look far for the best way to optimize this design.

--
David Portas
SQL Server MVP
--

28 Dec 2005 7:28 PM

Trey Walpole

why 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:
Show quote

> 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
> rafael
>
>
>