MySQL UPDATE query with subquery taking forever

Question

Hi,

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 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 :)

Thanks in advance.

Answer 1

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);

Answer 2

I'd go for something like:

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

Answer 3

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

Answer 4

"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;"