Database runs out of memory when large numbers of comments

[Mer0me]

This is related to https://github.com/nextcloud/server/issues/32549 and https://help.nextcloud.com/t/indexation-of-oc-comments-table-takes-ages-and-crash-mysql-out-of-memory/139352/2 I figure out that the recommendations app was the origin of the recurring reindexing of oc_comments table when having thousands of comments. On our instance with ~70K files, if we add just 2 comments per files, the database runs out of memory because each user opening a session (or simply displaying the files app ?) seems to throw recurring SQL commands like this :

SELECT * FROM oc_comments WHERE object_type = ‘files’ ORDER BY creation_timestamp DESC, id DESC LIMIT 100 OFFSET 76500

offset increasing 100 by 100 until we reach the end of the oc_comments table.

Disabling the recommendations App seems to solve the problem.

Maybe there is an optimization or code design problem here that you could fix ?

I'm not an expert but if it may help I think the problem takes place in these functions : getRecommendations() from RecommendationService.php getMostRecentRecommendation() from RecentlyCommentedFilesSources.php getCommentsPage() from RecentlyCommentedFilesSource class

The SQL request created here should limits results based on the timestamp (very old comments >X days should not being retrieved by theses functions, I guess ?)

	private function getCommentsPage(int $offset, int $pageSize): array {
		return $this->commentsManager->search(
			'',
			'files',
			'',
			'',
			$offset,
			$pageSize
		);
	}