WordPress.com REST API > Comment: audit and improve performance for comment counts

[jeherve]

seeing a case of Jetpack high resource utilization and breaking of site due to repeat query for comment counts

SELECT comment_approved, COUNT(*) AS num_comments
	FROM wp_siteid_comments
	WHERE comment_type IN ( 'comment','pingback','trackback','' )
	GROUP BY comment_approved

-- p1732041372458899-slack-C01U2KGS2PQ

Reference:

jetpack/projects/plugins/jetpack/class.json-api.php

Lines 1113 to 1121 in 78d4035

	// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- `$where` is built with escaping just above.
	$count = $wpdb->get_results(
	"SELECT comment_approved, COUNT(*) AS num_comments
	FROM $wpdb->comments
	{$where}
	GROUP BY comment_approved
	"
	);
	// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

Those direct queries were introduced in #10786 to allow us to include specific comment types in the counts.

The performance of the endpoint needs to be addressed so that it doesn’t lead to excessive query times and db connections becoming unavailable.
-- pdWQjU-156-p2

Seems like the initial first step may be to add caching for wp_count_comments in WPCOM_JSON_API, as @fgiannar had suggested in Slack at the time. Core's wp_count_comments does come with caching, but in some scenarios we do direct queries in the WordPress.com API instead for the reasons mentioned above.

Maybe it would also be worth looking at using get_comments() instead of doing a direct database query, since the underlying WP_Comment_Query does come with caching as well. This is what we do in other endpoints:

jetpack/projects/plugins/jetpack/json-endpoints/class.wpcom-json-api-list-comments-endpoint.php

Line 333 in f4c805e

$comments = get_comments( $query );