Improving JOIN with sub-query

Question

I capture Users who comment on the Articles of other Users, as

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

but since ArticleComments table is too large, the query is slow.

Is there any way to improve this query?

The tables are simple,

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;

Since both Articles and Comments can have multiple authors, I have to normalise the architecture.

Answer 1

Get a subtotal every day (or some other convenient unit of time). Put those subtotals in a "summary table".

Or run your IODKU only from where you left off last time, not from the start. Of course, it would need a small change;

ON DUPLICATE KEY UPDATE Freq = Freq + c.C

More on summary tables: http://mysql.rjweb.org/doc.php/summarytables