There are many ways to make your WordPress site faster, but seldom is the topic about your database. When your WordPress site starts to slog, it can be a frustrating experience for not only you, but your visitors as well. If a website is too slow, did you know it can actually lose potential customers? Really! According to Google, the probability of bounce increases 32% as page load time goes from 1 second to 3 seconds. Keeping your site quick isn’t just a vanity project; it has real consequences that can help grow your business.
For the uninitiated, every WordPress install has two parts: the PHP, CSS, and JS files that make up the functionality of WordPress, and the MySQL database that houses all of the information that propagates into the WordPress installation. A WordPress installation cannot function if it’s missing its database, and you’ve likely seen the dreaded “error establishing database connection” error before that tears down your entire site. So, let’s talk databases, identifying if they’re slow, and what you can do to fix them.
Is my WordPress database slowing down my site?
Slowness is not often attributed to the WordPress database. Usually, a slow site can be pinpointed easier to a slow host, code bloat, a bad theme, and too many plugins. However, in some instances, an overloaded database can bring a site to screeching halt. There are a few signs you can look out for to identify if you need to work on your database.
#1: Backups take a long time to complete or do not finish
If you have a backup solution like UpdraftPlus in place (you should have one!), you may notice when you click to back up your site, it takes a very long time to finish. If you pay attention to where in the process hangs up, you might notice it pauses on certain rows in the database. This is usually a good indicator that there’s a lot of rows in the database and you should look to see if you can do anything about it.
#2: Database server constantly crashes
This is not all indicative, but forcing a database server to handle multiple gigabytes of a database all the time can cause servers to crash, especially if they are a database server handled by a lesser-quality host or there’s only a small allocation of memory to the database server. Sometimes cleaning up your database can help mitigate crashing, but if you’re still having problems with it after working on your database, it may be time to consider moving.
#3: Database is huge
If you’ve looked through your hoster’s panel/dashboard and noticed that your database is multiple hundreds of megabytes or possibly even gigabytes, then it may be time to clean up and work on optimizing your WordPress database.
OK, now what?
Take a backup of your database. You will want to have a fresh copy in case anything goes wrong. If you can’t get your backup solution to take one, you will want to log into PhpMyAdmin and export your whole database. Trust me, you do not want to skip this step! If for any reason something goes wrong, it feels much better knowing you have something to fall back on.
What do I do once I suspect my WordPress database as the problem?
Your next step is identifying where and why your database is so slow. If you know anything about PhpMyAdmin, you can look in there to find which tables are bloated or have a lot of overhead. However, one of my favorite tools to use is the plugin WP Optimize. It gives you a lot of options with an easy to use interface, and it’s not just limited to the database. The WP Optimize plugin also offers minification and caching, which can overall help speed up your site. We’ll be focusing on the database offerings.
In WP Optimize, I like to look at the “tables” tab first. If you click on tables then scroll to the absolute bottom, you can see a total size of your database. If the amount is more than 300 MB, you may be looking at the potential for cutting it down.
In this same area, as you scroll through, the plugin will identify tables that belong to various plugins you may be using. It will also label tables that it thinks belong to certain plugins that you are not using or that may be left over from cleaning out plugins. Many of these tables can be removed right away, helping cut out excess and unnecessary tables. WP Optimize will tag in two ways, [not installed] and [not active]. A not active tag means you have the plugin installed but it’s not turned on, so you may want to keep those tables for now.
You can then click on “data size” twice to sort by largest table to smallest table. Tables with the most rows are highly likely to be the largest tables, and you can identify what plugin may be causing so many rows to be inserted into the table. After that, you can click “overhead” twice to sort by largest to smallest and see what tables may be storing rows that aren’t in use that may benefit from optimization.
If you’ve just got a bunch of overhead, you may be able to get away by clicking the “Optimizations” tab and running the first option – Optimize database tables. This will take a bit of time to process after you click it, so make sure to sit tight. Once that’s done, you can also run additional optimizations if you desire to clean up further.
If you weren’t able to pare down the database just by optimizing and removing unused tables, you’ll want to look at where all of the bloat is coming from. For example, I’ve usually seen a lot of rows inserted due to comments. If I’m seeing a comment table with hundreds of thousands of rows, I know to look at the comments. Often, I’ll find there’s thousands of spam comments that need to be deleted. This means that I have to purge all these spam comments, and also modify how long spam comments are kept (if at all). You can identify other plugins that have caused the same, and hopefully the developer has built tools within the plugin to dump data after certain periods of time.
If the table in question is the action scheduler, or a log table, you may want to log into PhpMyAdmin and run a SQL that deletes everything before or after a certain time period. Sometimes even modifying the plugin’s settings won’t retroactively clean up the database, so you may have to do some heavy lifting. You can even modify the action scheduler in your functions.php to delete items after so many seconds, which you may want to do if you keep seeing the action scheduler table grow larger after you’ve cleaned it out.
After all is said and done, your site should still be functioning (make sure to test it!) and your database should be a lot slimmer. Make sure to take a backup of this state, and look at it in a week or so to ensure it hasn’t bloated back up to where it was before. If it returns to a bloated size, you may want to consider replacing plugins or other options that are adding too many rows to your database.
Hopefully this helps you to clean up your WordPress database and speed up your site. Have questions? Let me know in the comments.