Optimize UPDATE JOIN query in MYSQL

Question

I am running the following SQL query:

UPDATE A 
  join B 
    on A.b_id  = B.id 
   SET B.col1 = 'val1'
     , B.col2 = ''
     , B.col3 = 1
 where a.col4 = 'val4' 
   and b.col5 = 1234 
   and b.col6 = 1 
   and b.col7 = 'val7';

In table B, id is PRIMARY KEY of B, in table A. b_id is just a column in A supposed to have same value as id of table B.

My question is, is there any way to optimize the above query with/without (preferable) changing table definition ? Thanks.

Answer 1

You use SUBQUERY to perform JOIN to reduce the number of rows involved to the JOIN.

Since you only update table B's column , so you only put B at UPDATE clause. INNER JOIN was suggested to prevent some unwanted updates.

The enhancement shown as below:-

 UPDATE B SET B.col1 = 'val1', B.col2 = '', B.col3 = 1 
     FROM (SELECT b_id FROM A WHERE a.col4='val4') as tableA
     INNER JOIN B ON tableA.b_id  = B.id 
     WHERE b.col5=1234 AND b.col6=1 AND b.col7='val7';

Answer 2

    on A.b_id  = B.id 
   SET B ...
   WHERE
   and b.col5 = 1234 
   and b.col6 = 1 
   and b.col7 = 'val7';

I don't know whether it will start with A or B. So, let me recommend indexes for each:

B:  INDEX(col5, col6, col7, id)
A:  INDEX(b_id)
A:  INDEX(col4, b_id)

Those will also help Ronnie's subquery approach.

If you have a new enough version, please provide EXPLAIN UPDATE ... both before and after adding these indexes. (Old versions of MySQL don't have that command.)