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.
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.