0

I have a simple tag_map table

CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID) ON DELETE CASCADE,
PRIMARY KEY(TagID,ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1

I get the co-tags (tags, which appear along with a specific tag):

SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
) 
GROUP BY TagID

How can I do this query for all tags to get

TagID1,TagID2,COUNT(*)

The table is huge (10-50M rows) and each article has tens of tags. Thus, performance is critical.

3
SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

UPDATED: index on ArticleID may help to improve query performance

| improve this answer | |
  • Terminology for the technique: "self-join". – Rick James Jun 3 at 19:19
  • 1
    Depending on the requirements, the output could be cut in half. Currently it has [1,2,count] and [2,1,count]. Change <> to <. – Rick James Jun 3 at 19:21
  • A "covering" index on (ArticleID, TagID) will further help performance. (Instead of just (ArticleID).) – Rick James Jun 3 at 19:23
  • @RickJames TagID is a part of the clustered index, index on ArticleID will include this column anyway – NikitaSerbskiy Jun 3 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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