使用子查詢改進 JOIN

我擷取對其他使用者的文章發表評論的使用者，如

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，而不是從一開始。當然，它需要一點點改變；

ON DUPLICATE KEY UPDATE Freq = Freq + c.C

更多關於匯總表：http: //mysql.rjweb.org/doc.php/summarytables

引用自：https://dba.stackexchange.com/questions/302495