0

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.

5
  • It is just a guess but is this running while you are logged into the admin part of the site? To me that query is simply counting approved comments, but the group by "should" make that query instant if the table is indexed correctly.
    – Shawn
    Jan 22, 2014 at 17:30
  • seems if you can get the comment_count fast, without type, you may be able to process it after it's retrieved? might be only option with given indexes
    – GhostToast
    Jan 22, 2014 at 21:26
  • @Shawn The table appears to be indexed, but is very slow. I'll check it more closely and see why the compound index is not helping it. I was logged in as an admin, and using the debug bar to see the query, but the query was on a single WC product page. Just not had a chance to dig further into the code, but will report back when I do.
    – Jason
    Jan 22, 2014 at 21:29
  • Oh, the cache is not persistent, without installing a plugin to make it persistent. That is why the query is run on every page, even though it is cached (codex.wordpress.org/Class_Reference/WP_Object_Cache). TIL.
    – Jason
    Jan 23, 2014 at 2:29
  • To follow up this issue, adding an index to wp_comments.comment_type can reduce the time of this query substantially. With another WC site having 50,000 order updates (all in the wp_comments table) that index has shaved off around 200mS from every page load. WC injects where comment_type != 'order_note ' into the core WP get_comment_count() query using a filter, so that column needs to be indexed to avoid a full table scan every time WP wants to display the number of comments pending/against a post etc.
    – Jason
    Nov 9, 2015 at 15:08

1 Answer 1

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.

5
  • I'll get some more details and try running the optimize (I was a bit rushed before, so did not get a chance to post the explain results).
    – Jason
    Jan 22, 2014 at 21:32
  • 300 ms is quite fast. Why are you focusing on this query specifically?
    – Otto
    Jan 23, 2014 at 4:18
  • 300mS of a single flat-out database query on every single page load is not fast at all, especially on a busy site. Most queries run in around 1mS, and this one stands out above the rest. I think some persistent object cacheing is going to be way forward for me today, but it does raise many questions about the assumptions both WP and plugin developers make on the way the comments table is used - things don't quite line up (i.e. custom comment types means that not all the rows in the table need be strictly comments, but the WP query assumes they are ALL public comments).
    – Jason
    Jan 23, 2014 at 10:41
  • This one query takes up 30% of the total database load (based on the time it takes to run) so is significant enough to tackle. I think in the end it is going to be WooCommerce that needs to be fixed so it does not used the comments table to hold order notes (of which there will be at least three per order). It is an expensive query when the comments table begins to get big. Thanks for the info so far - I like to get to the bottom of things to see where the problem really lies, because just throwing cache at an application can mean bugs persist for years instead of being tackled head-on.
    – Jason
    Jan 23, 2014 at 10:48
  • Yes, it is very much assuming that things in the comments table are public. They're comments. If you're using the comments table in a different way, then you're probably going to have issues long term. Custom post types might be better suited to deal with that, or use a custom table entirely. But I can't see how a query counting the elements in the table would be slow for any particular reason.
    – Otto
    Jan 23, 2014 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.