query optimization

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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  

UPDATE    Repair_Order_Lines l ,
          priceMaster        p
SET       l.IsOEM            = 1
WHERE     l.IsOEM            = 0
          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.



Re: query optimization

Quoted text here. Click to load it

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:

  `Repair_Order_Lines` AS `l`
  `priceMaster` AS `p`
  `l`.`PartNumber` = `p`.`PartNumber` OR
  `l`.`PartNumber` = `p`.`PrecedentPartNumber`
  `l`.`IsOEM` = 1
  `l`. IsOEM = 0

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:

-Mike PII

Site Timeline