我有一个简单的 tag_map 表
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
我得到了共同标签(标签,与特定标签一起出现):
SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
)
GROUP BY TagID
如何对所有标签进行此查询以获取
TagID1,TagID2,COUNT(*)
表格很大(10-50M 行),每篇文章有几十个标签。因此,性能至关重要。
更新:ArticleID 上的索引可能有助于提高查询性能