MySQL UPDATE查询与子查询永远在一起 [英] MySQL UPDATE query with subquery taking forever

问题描述

我有一个MySQL UPDATE查询，需要很长时间才能完成.我是否错过了一种更简单的方法来获得相同的结果?

I have a MySQL UPDATE query which takes a long time to complete. Am I missing a much simpler way to achieve the same result?

"UPDATE table2, table1
SET table2.id_occurrences = (SELECT SUM(IF(id = table2.id, 1, 0)) FROM table1)
WHERE table2.id = table1.id;"

• table2包含id的所有可能值，每个值恰好一个记录.
• table1包含一些id值，但是有多个记录包含这些值.
• 我需要更新table2中的记录以显示table1中相应id值的出现次数.上面的查询可以完成工作，但是当table1包含500条记录和table2 30,000条记录时，大约需要3分钟.我有更大的表要处理，所以这太长了:)
• table2 contains all possible values of id, exactly one record for each.
• table1 contains some values of id, but there are multiple records of some values.
• I need to update records in table2 to show the number of occurrences of the corresponding value of id in table1. The above query does the job, but it takes about 3 minutes when table1 contains 500 records, and table2 30,000 records. I have much bigger tables to process so this is too long :)

谢谢.

推荐答案

避免子查询，请使用联接:

Avoid subqueries, use joins:

UPDATE table2
LEFT JOIN table1 ON (table2.id = table1.id)
SET table2.id_occurrences = COUNT(table1.id)
GROUP BY table2.id

哦，UPDATE不支持GROUP BY.尝试以下查询:

Oh, UPDATE doesn't support GROUP BY. Try this query:

UPDATE table2
LEFT JOIN (
   SELECT id, COUNT(*) AS cnt FROM table1 GROUP BY id
) AS t1
ON (table2.id = t1.id)
SET table2.id_occurrences = t1.cnt

这篇关于MySQL UPDATE查询与子查询永远在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！