MySQL update join performance

Question

I need to update several columns in one table, based on columns in another. To start with I am just updating one of them. I have tried 2 ways of doing this, which both work, but they are taking about 4 minutes using mySQL commands, and over 20 when run in php. Both tables are about 20,000 rows long.

My question is, is there a better or more efficient way of doing this?

Method 1:

   UPDATE table_a,table_b 
   SET table_a.price = table_b.price 
   WHERE table_a.product_code=table_b.product_code

Method 2:

   UPDATE table_a INNER JOIN table_b 
   ON table_a.product_code = table_b.product_code
   SET table_a.price=table_b.price

I guess that these basically work in the same way, but I thought that the join would be more efficient. The product_code column is random text, albeit unique and every row matches one in the other table.

Anything else I can try?

Thanks

UPDATE: This was resolved by creating an index e.g.

    CREATE UNIQUE INDEX index_code on table_a (product_code)
    CREATE UNIQUE INDEX index_code on table_b (product_code)

Answer 1

If your queries are running slowly you'll have to examine the data that query is using.

Your query looks like this:

UPDATE table_a INNER JOIN table_b 
ON table_a.product_code = table_b.product_code
SET table_a.price=table_b.price

In order to see where the delay is you can do

EXPLAIN SELECT a.price, b.price FROM table_b b
INNER JOIN table_a a ON (a.product_code = b.product_code)

This will tell you if indexes are being used, see the info on EXPLAIN and more info here.

In your case you don't have any indexes (possible keys = null) forcing MySQL to do a full table scan.

You should always do an explain select on your queries when slowness is an issue. You'll have to convert non-select queries to a select, but that's not difficult, just list all the changed fields in the select clause and copy join and where clauses over as is.