doumaque6551
2014-09-22 10:40
浏览 427
已采纳

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

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.

图片转代码服务由CSDN问答提供 功能建议

我坚持通过与php / Mysql中的另一个表进行比较来更新一列表。 我试图通过索引表列,优化查询等但无法加快进程来加快进程。

在我的基于php的应用程序中有两个表(表A和表B),我想通过与表B比较来更新表A的一列(有两个列 - 名称& SKU)。

以前上述过程最多需要15分钟才能更新28k产品。 但现在两个表(表A和表B)都有60k行。 现在需要两个多小时。 我使用了以下查询

  mysql_query(“UPDATE tableA a 
 JOIN tableB b ON a.product_code_sku = b.sku 
 SET a.is_existing_product ='1'”) 或者死(mysql_error()); 
 
mysql_query(“UPDATE tableA a 
 JOIN tableB b ON a.product_name = b.product_name 
 SET a.is_existing_product ='1'”)或die(mysql_error());  
   
 
 

上面的查询非常慢,之后我更改了下面的更新过程

  $ query_result = mysql_query  (“SELECT t1 .id`,t2 .product_id` FROM`tableA` t1,
`tableB` t2 where(t1.product_code_sku = t2.sku 
或t1.product_name = t2.product_name)”)或死 (mysql_error()); 
while($ result_row = mysql_fetch_array($ query_result))
 {
 mysql_query(“UPDATE`tableA` SET is_existing_product ='1'
其中id ='”。$ result_row ['id'  ]。''“)或死(mysql_error());  
} 
   
 
 

但我所有的努力都是徒劳的。

请告诉我如何加快处理速度。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新