我捕获对其他用户的文章发表评论的用户,如
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID AS AuthorID,c.UserID AS CommentatorID,COUNT(*) AS C
FROM ArticleComments a
JOIN ArticleMap b ON a.ArticleID=b.ArticleID
JOIN CommentMap c ON a.CommentID=c.CommentID
GROUP BY b.UserID,c.UserID
) c
ON DUPLICATE KEY UPDATE Freq=c.C
但由于ArticleComments
表太大,查询速度很慢。
有什么办法可以改进这个查询吗?
桌子很简单,
CREATE TABLE AuthorCommentators
(
AuthorID int(11) unsigned NOT NULL,
CommentatorID int(11) unsigned NOT NULL,
Freq mediumint(7) unsigned,
INDEX(CommentatorID),
INDEX(Freq),
PRIMARY KEY(AuthorID,CommentatorID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE ArticleComments
(
ArticleID int(11) unsigned NOT NULL,
CommentID int(11) unsigned NOT NULL,
INDEX(CommentID),
PRIMARY KEY(ArticleID,CommentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE ArticleMap
(
ArticleID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(ArticleID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE CommentMap
(
CommentID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(CommentID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
由于两者都Articles
可以Comments
有多个作者,我必须规范化架构。
每天(或其他方便的时间单位)获取小计。将这些小计放在“汇总表”中。
或者仅从上次停止的地方运行您的 IODKU,而不是从一开始。当然,它需要一点点改变;
更多关于汇总表:http: //mysql.rjweb.org/doc.php/summarytables