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)

They're both the same. Run EXPLAIN EXTENDED on the first query, followed by SHOW WARNINGS; — Strawberry, May 17 '15 at 22:25
well they're not _exactly_ the same, since one of them is setting the id, the other is setting the price. there's no difference in performance between implicit and explicit join syntax tho — pala_, May 17 '15 at 23:20
Sorry it was late at night, they do both set the price, have edited above. So they both match up the IDs, and then set the price from table_b in table_a. — cloudhal, May 18 '15 at 05:42
1) remove all indexes on these tables except the ID (which i assume is primary) 2) run your update 3) add again the indexes. you need to do this so mysql doesnt have to rebuild indexes on each update. (20000 times). if you are using innoDB you can just http://stackoverflow.com/a/24323049/953684 — Sharky, May 18 '15 at 05:42
The ID field is in fact like a product code and is a VARCHAR. I will look at creating an index and see if that helps. There is also a primary key, but these dont match up at all. — cloudhal, May 18 '15 at 05:50
The values in the ID column I am matching look like this: CTG-80219,RIVA-1400-LRPU-BLUE,SSD-SSDSC2BP240G4 and so on. So they are pretty random character strings. Should I create a hash index of these or something? — cloudhal, May 18 '15 at 06:09
@fbas the index tip was what I needed (haven't got that far on the SQL course yet!) - created on and update went from 4 minutes to less than 1 second. Thanks — cloudhal, May 18 '15 at 07:50
that's disappointing (in the class). They should drill into your head to at least set unique primary key indexes in all tables (unless and until you know when not to). All the same, you learned that pretty quickly and see the effect. Glad to help. — fbas, May 18 '15 at 14:36
You might want to add you resolution as an answer and then mark it as the accepted answer. This way if your question shows up on a search (SO, google or bing) pepole can see that it has answer and will more likely see what it was. — Conrad Frix, Jun 25 '15 at 18:35

score 2 · Answer 1 · answered Jul 16 '16 at 13:32

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.

MySQL update join performance

1 Answers1