In the same project I blogged about last week, I was tasked to optimize the performance of the website. One of the major issues was the login performance. Any time I would log in, I had to wait around one minute before I could see the dashboard. But what was wrong with the site, that it was so slow?

An enormous database

When I’ve started analyzing the site, I first had to set it up locally. This alone took me several days! The database was so large, I literally had to upgrade the SSD in my laptop, before I could even think about importing it. The database dump was “only” around 40 GB in size, but after the import, I used up around 100 GB on my SSD. Since I wanted to optimize it and have backups, I copied the whole database twice in the end, as I also did an HPOS migration. So after spending hours installing a new SSD, copying my whole system (a Windows/Linux dual boot), fixing a broken bootloader and running the database import, I was finally ready to start analyzing the page.

Finding the performance bottleneck for the login

After my first login, the Query Monitor plugin showed me two queries that took extremely long. This is what they looked like:

SELECT COUNT(*)
FROM   wp_comments
WHERE  user_id = 123456
AND    comment_approved = 1

Two of those queries took around 30 seconds each. This increased the login time by one full minute. Query Monitor also gave me the info which “Caller” executed those queries. It was the Akismet::get_user_comments_approved function.

The reason for those queries

The shop was using Akismet to fight comment spam. In the “At a Glance” widget on the dashboard, it shows the number of comments that are in the spam queue, and it shows how many are in moderation. In the “Activity” widget, it also shows “Recent Comments”. For any comment that is shown in this list, and that was made by a user/customer of the site, it would get the number of approved comments. The joke is: this value is added to the dashboard widget, but then hidden with a display:none inline style, so it doesn’t even get shown. Those queries still run. Fortunately, it was only two users, and not all five that would be listed, otherwise the login would have been slowed down by two and a half minutes.

Analyzing the issue

Now that we know which query is causing the performance issue, and we know why this query is happening, how can we fix it? Let’s take a look at why this query is so slow.

The shop was a multisite WordPress installation. In one of the shops, we had around 76,600 customers (the other shop had almost twice as much) with more than 83,000 subscriptions resulting in more than 1,442,000 orders. Yes, that’s way more than one million orders, and this is just for one of the sites. So you could say, that this is quite a large shop. Every order has some “meta information”. One of this information is “order notes”, and those are stored as comments in the same table as regular comments. In the comments table, we had a total 15,348,283 comments! Those were the types:

Now, when we look at the query causing this slow login, we can recognize that if filters the comments by the user_id and comment_approved columns. Looking at the structure of the table, we have an index comment_approved_date_gmt that can be used for the comment_approved column, but we do not have an index on the user_id column. This means, that MySQL needs to look at all the more than 15 million comments, to find the ones for the user_id we have in the query. And it does that for every single user_id we have. This might take a while.

Fixing the issue

As we now know the reason for that slow query, we can try to find a solution to solve it. There are three solutions that might work:

Solution 1: Delete the comments

This might be a bit too radical, but for this shop it would even work. Interestingly, the two comments slowing down the site were made on the “Checkout” page. That’s not a page you would usually allow comments on. Deleting those to comments (or at least moving them to “Trash”) resolved the login issue, as they were no longer showing up in the widget under “Recent comments”.

Solution 2: Exclude comment types

In preparation of this blog post, I’ve found a filter that can be used to improve the query. For the query to count comments for a user_id, you can exclude comment types using a filter like this:

function my_excluded_comment_types($comment_types) {
	$comment_types[] = 'order_note';
	$comment_types[] = 'user_membership_note';

	return $comment_types;
}
add_filter( 'akismet_excluded_comment_types', 'my_excluded_comment_types' );

Since the order_note and user_membership_note types are the ones we really don’t need, we should exclude them. WooCommerce adds an index for the comment_type column in the wp_comments table, allowing MySQL to filter out some comment types very effectively.

This improves the 30-second query to around 0.015–0.03 seconds only. Quite an improvement already. We only need to make sure to exclude all the comment types we don’t need. Depending on the plugins you are using, there might be other comment types you want to exclude as well.

Solution 3: Add an index to the user_id column

As mentioned earlier, the issue with this query is that we are trying to filter by user_id, but this column does not have an index. Adding one can also resolve the issue. To add an index, run this query on the database (e.g. with phpMyAdmin, Adminer, the WP-CLI or some other tools):

ALTER TABLE wp_comments ADD INDEX `my_idx_user_id` (`user_id`);

If you are using a “prefix” other than “wp_“, change the query accordingly. Be aware that adding an index to a table might take a while. For those 15 million entries, it took 30–35 seconds to create the index. In this time, writing to the table might be slower or even blocked, so better run it at a time with lower activity in the shop.

After adding an index, the initial 30-second query now only takes 0.001 seconds (or even less, since that’s the lowest number I would get when analyzing queries), so it’s even more than 10 times faster as with the previous solution.

Conclusion

Performance issues on large websites and shops are a very different story than for small sites. At least my blog is far away from reaching millions of comments. 😁

Finding the cause of a performance issue and how to fix it is also not always an easy and straightforward task. Using a tool like the Query Monitor plugin usually helps a lot to find those issues. But finding good solutions can be hard, especially when the component causing the issue does not offer an action or filter to solve it, or you are unable to fix it differently, like adding an index. For the shop mentioned in this blog post, I’ve ended up using solutions 1 and 3, while I had to use a custom WP-CLI command to add the index to the database, because I had no write access using tools like phpMyAdmin or Adminer.

Have you also experienced some “interesting” performance issues with large site or shops? Then please share them in a comment. Or maybe two, but please not a million comments. 😂