slow UPDATE of rows by COUNTing from another table I try to update a table by counting rows from another table as

UPDATE t1 SET c1 = (
    SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
    )


strangely, this query stuck at Sending data state for hours.

SELECT COUNT(*) FROM t2


returns ~16M in 2 seconds.

SELECT id FROM t2 GROUP BY id


returns 50K rows in 5 seconds.

The table structures are very simple

CREATE TABLE t2
(
item int(11) unsigned NOT NULL,
id mediumint(7) unsigned NOT NULL,
PRIMARY KEY (item,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci
Remove correlation:

UPDATE t1 
JOIN ( SELECT t2.id, COUNT(*) c1 
       FROM t2
       GROUP BY t2.id ) t3 ON t3.id=t1.id
SET t1.c1 = t3.c1;