0

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         | <derived2>  | 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)
6
  • On the face of it, a.Tags IS NULL can have nothing to do with the subquery as it's not referencing the subquery's columns. However, if you think that the plan shape that MySQL generated for the update is somehow connected to that condition's not being applied at the join time, then why not try moving the condition from WHERE to ON, as in ON a.ID=b.ID AND a.Tags IS NULL? I have no idea if this will work, I just see this as an obvious thing to try first.
    – Andriy M
    Commented May 24, 2021 at 11:41
  • @AndriyM I don't think it works, before the JOIN, the table b is formed by the subquery. The solution is to add another subquery inside the subquery as WHERE ID IN(SELECT ID FROM articles WHERE Tags IS NULL), but I think it is overkill.
    – Googlebot
    Commented May 24, 2021 at 11:45
  • @Googlebot - Before I launch into trying to optimize the Update, see if one of the Answers works well for your case. Meanwhile, please provide SHOW CREATE TABLE for each of the 2 tables; something looks fishy in that EXPLAIN.
    – Rick James
    Commented May 25, 2021 at 18:09
  • @RickJames I added SHOW CREATE TABLE. I am benchmarking the given answers to find if they noticeably improve the query.
    – Googlebot
    Commented May 26, 2021 at 12:07
  • The name "Tags" implies that there may be multiple tags for an article, yet the schema allows only one. Is an "article" a "question"? (Sorry, but column names should be picked to help, not hinder, understandability of the schema.)
    – Rick James
    Commented May 26, 2021 at 14:58

2 Answers 2

3
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;
0
2

When the percent of rows to be updated is low then correlated subquery is the best option:

UPDATE articles a
SET a.Tags = ( SELECT COUNT(*)
               FROM tag_map b
               WHERE a.ID = b.ID )
WHERE a.Tags IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.