Mysql根据另一个表更新一个表列大量数据

Question

I am stuck to update one column of table by comparing with another table in php/Mysql. I have tried to speed up the process by indexing the table columns, optimizing the query etc but unable to speed up the process.

In my php based application there is two table (table A and table B) , I want to update one column of table A by comparing with table B (with two column - name & sku).

Previously above process has taken max 15 mints to update 28k products. But now both table (table A and table B) have 60k rows. Now it's taking more than two hours. I have used below query

mysql_query("UPDATE tableA a 
            JOIN tableB b ON a.product_code_sku = b.sku 
            SET a.is_existing_product = '1'") or die(mysql_error());

mysql_query("UPDATE tableA a 
       JOIN tableB b ON a.product_name = b.product_name 
       SET a.is_existing_product = '1'") or die(mysql_error()); 

Above query was very slow after that I have changed the updating process like below

$query_result = mysql_query("SELECT t1.`id`,t2.`product_id` FROM `tableA` t1,
                     `tableB` t2 where (t1.product_code_sku = t2.sku
                      or t1.product_name = t2.product_name)") or die (mysql_error());
while($result_row = mysql_fetch_array($query_result))    
{
    mysql_query("UPDATE `tableA` SET is_existing_product = '1' 
               where id = '".$result_row['id']."' ") or die (mysql_error());  
}

But all of my efforts are in vain.

Please advice me how to make the process faster.

Answer 1

Your first update query and the second update query is doing two different thing. The second query is slower because you are using a OR for comparison.

You can consider to create a temporary table to compare and insert, the update back to tableA.

First and all, you should examine the execution for the two join queries, like

desc select a.id
from tableA a 
join tableB b ON a.product_code_sku = b.sku;

If this is the reason why the update is slow, you should optimize the query. Otherwise, you can try the below:

For instance (assuming ID the primary key),

// make sure the columns are in the same data type
create table tmp_sku (
  id .. // just the primary key, make sure is using the same data type as in tableA
);

// do a insert into this temporary table
insert into tmp_sku select a.id
from tableA a 
join tableB b ON a.product_code_sku = b.sku;

// now we have list of matches,
// then do a insert .. duplicate key update
// by comparing the primary id
insert into tableA (id, is_existing_product)
select tmp_sku.id, 1 from tmp_sku
on duplicate key set is_existing_product = 1;

// repeat for the product name
truncate tmp_sku;
insert into tmp_sku
select a.id
from tableA a 
join tableB b ON a.product_name = b.product_name;

// repeat the duplicate .. update
insert into tableA (id, is_existing_product)
select tmp_sku.id, 1 from tmp_sku
on duplicate key set is_existing_product = 1;