query optimization

[Steve]

sorry for the ot-ish post.

i've got a repairs table and a price master table. each has millions of
rows. i'm trying to optimize the query below. i think the best way would be
to use an update statement with a join but i can't remember the syntax. any
thoughts?

UPDATE Repair_Order_Li nes l ,
priceMaster p
SET l.IsOEM = 1
WHERE l.IsOEM = 0
AND
(
l.PartNumber = p.PartNumber
OR l.PartNumber = p.PrecedentPart Number
)

essentially, if a repair line has a part number in the price master table,
it is considered an OEM part. both the PartNumber columns and the
PrecedentPartNu mber column are VARCHAR(50). priceMaster is indexed on both.

tia,

me

[Mike P2]

Re: query optimization

UPDATE Repair_Order_Li nes l ,

priceMaster p
SET l.IsOEM = 1
WHERE l.IsOEM = 0
AND
(
l.PartNumber = p.PartNumber
OR l.PartNumber = p.PrecedentPart Number
)

In MySQL (4 and 5, or maybe just 5), using a comma is semantically
identical to using an inner join. I've noticed that I can't use ON
when using a comma, though. If you want to use a join in a way that
might be faster, try this:

UPDATE
`Repair_Order_L ines` AS `l`
INNER JOIN
`priceMaster` AS `p`
ON
`l`.`PartNumber ` = `p`.`PartNumber ` OR
`l`.`PartNumber ` = `p`.`PrecedentP artNumber`
SET
`l`.`IsOEM` = 1
WHERE
`l`. IsOEM = 0
ORDER BY NULL

As this is an update, you should of coarse probably use this on test
data before trying it out.

You should probably have a primary index on
`Repair_Order_L ines`.`PartNumb er`, this is a good index for the query
to be using, so it might be a good index hint to use. Also, having too
many indexes will hurt performance of UPDATEs, so look at your indexes
and reconsider whether or not they are all useful.

Check this out:

http://dev.mysql.com/doc/refman/5.1/en/update-speed.html

-Mike PII