How to UPDATE faster with GROUP BY? I have a typical article and tag_map tables,
CREATE TABLE `articles` (
  `ID` int(11) unsigned NOT NULL,
  `Tags` tinyint(3) unsigned DEFAULT NULL,
  `Title` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Tags` (`Tags`),
  FULLTEXT KEY `Title` (`Title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'


CREATE TABLE `tag_map` (
  `TagID` int(11) unsigned NOT NULL,
  `ID` int(11) unsigned NOT NULL,
  `Number` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`TagID`,`ID`),
  KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `PAGE_COMPRESSED`='ON'

and I need to UPDATE the article table with the number of tags COUNTed from tag_map table as
UPDATE articles a JOIN (
SELECT ID,COUNT(*) AS C FROM tag_map GROUP BY ID
) b ON a.ID=b.ID 
SET a.Tags=b.C WHERE a.Tags IS NULL

The problem is that WHERE a.Tags IS NULL condition is not applied to the subsequery. When updating only a few roles in the articles table, the entire tag_map table is scanned.
I run MariaDB 10.5, and this is the output of EXPLAIN UPDATE:
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
| id   | select_type     | table       | type | possible_keys | key        | key_len | ref                   | rows     | Extra       |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
|    1 | PRIMARY         | a           | ALL  | PRIMARY,Tags  | NULL       | NULL    | NULL                  | 34543450 | Using where |
|    1 | PRIMARY         | &lt;derived2&gt;  | ref  | key0          | key0       | 4       | texts.a.ID            | 2        |             |
|    2 | LATERAL DERIVED | tag_map     | ref  | ID            | ID         | 4       | texts.a.ID            | 3        | Using index |
+------+-----------------+-------------+------+---------------+------------+---------+-----------------------+----------+-------------+
3 rows in set (0.001 sec)
UPDATE articles a JOIN ( 
  SELECT a2.ID,COUNT(*) AS C 
  FROM articles a2 JOIN tag_map b2 ON a2.id=b2.id 
  WHERE a2.tags IS NULL
  GROUP BY a2.ID 
) b ON a.ID=b.ID  
SET a.Tags=b.C 
WHERE a.Tags IS NULL;