What is the point of get_comment_count() if you cannot limit by a comment type?

Question

We are finding that large WooCommerce sites run very slow. Amongst many issues, this one has me scratching my head.

WooComemrce stores order notes in the comments table, giving them a comment_type of 'order_note' and approving them immediately. They are linked to the orders so can only be seen by the shop administrators.

Now, something on one site (not traced it yet) is doing this query:

SELECT comment_approved, COUNT(*) AS total
FROM wp_comments
GROUP BY comment_approved

With 120k comments on the system, mostly of type "order_note", this is taking a substantial time on every page load (up to 500mS query time) and thrashing the server. What would be the point of this query? It gets the counts of ALL rows in the comments table, grouped by the approval status. It counts comments, trackbacks, pings, and order_notes. What would be the point of that? I cannot think of a use for doing such a query on every page.

The get_comment_count() function in wp-includes/comment.php will run this query. If you pass in the post ID, then it summarizes for that post. That's fast. If you do not pass in a post ID, then it counts all rows in the entire table, and that is slow and IMO useless. This function does NOT accept a comment_type, so you cannot limit the query to a specific type of comment.

While I trace the source of this query, I am wondering why WP would support querying the whole table like this. It is kind of assuming that custom comment types will NEVER be created, i.e. the comments table will never be used for anything other than the three comment types listed in the codex. Is this the case? Is WooCommerce totally abusing this table?

I'm not sure what kind of answer I am looking for here, but I suspect a ticket needs to be raised for it somewhere (perhaps adding the comment_type to the query WHERE clause in the function), so looking for any thoughts or any similar experiences with this problematic query.

Updates:

The exact query:

SELECT comment_approved, COUNT( * ) AS num_comments
FROM wp_comments
GROUP BY comment_approved

This takes 300mS consistently to run, and returns this:

| comment_approved | num_comments | | ---------------- | ------------ | | 0 | 1 | | 1 | 191370 | | post-trashed | 1 | | spam | 29 |

No difference after optimising the table. Now I noticed the count aliases as "num_comments" and not "total", which means it is wp_count_comments() that is running and not get_comment_count(). This means the count results should be cached under key comments-0, but it's not - the query is run every page. Maybe the cache is being cleared somwhere else on the page?

But still, doing this count is useless. Most of the "approved" comments are order_notes added by WooCommerce. They are not "comments" in the usual sense.

The comment_approved column is indexed (the first column in a compound index) and WP says it has a cardinality of 7, even though only four groups are returned from the query. This takes about 1mS:

SELECT DISTINCT comment_approved
FROM wp_comments

So the index is working there, but it just slows down when the counts are needed, and that should really only need the indexes to be inspected.

The explain plan is:

select_type = SIMPLE table = wp_comments type = index possible_keys = NULL key = comment_approved_date_gmt (???) key_len = 70 ref = NULL rows = 195885 Extra = Using index

Maybe it is just one of those things that MySQL is really bad at optimising, like sub-selects, which it have been atrocious since day one.

Answer 1

That query is run in the get_comment_count() function in core. The wp_count_comments() function runs a similar query, but it caches the resulting data using the wp_cache system (so it won't run that code more than once per request). get_comment_count() does not use the cache.

Thing is, this seems to me to be a perfectly a valid query. It runs that in order to get the count of moderated, approved, trashed, and so forth comments.

Now, the comment_approved is an indexed key in that table, so realistically, the query should be quite fast. When I run an EXPLAIN on that query, MySQL tells me that it's a SIMPLE select with "Using index" on the comment_approved_date_gmt key.

So, it's not a slow query by any means, and the comment type shouldn't really make any difference. If it's slow for you, then perhaps something is wrong with your comments table. Are the indexes properly made? Try running an OPTIMIZE on the table. Although, since that query should only really hit the index and not necessarily the table, I wouldn't think that would matter.

Speed of a query isn't a matter of the number of records you're querying against, it's a matter of indexing and how much of the table needs to be accessed to produce the result.

In case of doubt on a query, always manually run it through EXPLAIN to see what the problem actually is. Like so:

EXPLAIN SELECT comment_approved, COUNT(*) AS total
FROM wp_comments
GROUP BY comment_approved

Additional: Note that the comment_type field is NOT indexed, so adding a WHERE clause with that field would probably make the query much slower. This may be a valid concern, but the concern would be with the table indexing, not with the query.