开发者

MySQL UPDATE query with subquery taking forever

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 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 r开发者_如何转开发ecords 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 :)

Thanks in advance.


I think your join on the update is perhaps not necessary...

UPDATE table2
    SET table2.id_occurrences = (SELECT COUNT(*) FROM table1
                                     WHERE table2.id = table1.id);


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

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


I'd go for something like:

UPDATE table2
SET id_occurrences = (SELECT count(*) FROM table1
                      WHERE table1.id = table2.id)


UPDATE table2, table1 
SET table2.id_occurrences = (SELECT SUM(IF(id = table2.id, 1, 0)) FROM table1) 
WHERE table2.id in (select distinct table1.id from table1) AND table2.id = table1.id;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜