By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,304 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,304 IT Pros & Developers. It's quick & easy.

query optimization

P: n/a
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_Lines l ,
priceMaster p
SET l.IsOEM = 1
WHERE l.IsOEM = 0
AND
(
l.PartNumber = p.PartNumber
OR l.PartNumber = p.PrecedentPartNumber
)

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
PrecedentPartNumber column are VARCHAR(50). priceMaster is indexed on both.

tia,

me
May 2 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
UPDATE Repair_Order_Lines l ,
priceMaster p
SET l.IsOEM = 1
WHERE l.IsOEM = 0
AND
(
l.PartNumber = p.PartNumber
OR l.PartNumber = p.PrecedentPartNumber
)
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_Lines` AS `l`
INNER JOIN
`priceMaster` AS `p`
ON
`l`.`PartNumber` = `p`.`PartNumber` OR
`l`.`PartNumber` = `p`.`PrecedentPartNumber`
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_Lines`.`PartNumber`, 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/...ate-speed.html

-Mike PII

May 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.