WordPress database optimisation

In this article, I’ll write about my experience with WordPress database optimisation. What has worked for me so far, and what I recommend based on that experience. I’ll keep this simple and novice-friendly, using plugins.

Table Of Contents (T.O.C.):

1. Why is this important?
2. LiteSpeed Cache plugin optimisation
3. Advanced Database Cleaner Pro optimisation
4. Index WP MySQL For Speed
5. Conclusion?

1. Why is this important?

If left by itself, WordPress is not very good with database optimisation. So, over time, as you add more articles, comments, authors etc, your website will slow down. This will put an extra load on your hosting server (forcing you to pay for more expensive web hosting), and make your website user experience worse (which is bad for SEO).

Fortunately, today we have plugins to help fix this problem and improve your WordPress website speed. Without much talk, I’ll go straight to step-by-step instructions for database optimisation:

Back up your database before doing anything!

– T.O.C. –

2. LiteSpeed Cache plugin optimisation

Back up your database before doing anything!

Even if you are not on a LiteSpeed server (with WordPress, you should be – just get some good hosting 🙂 ), you can use the LiteSpeed Cache plugin to optimise and clean up your database. To start, install and activate the LiteSpeed Cache plugin (WP.org link) if you haven’t already.

How to install and activate a WordPress plugin.

In a separate article, I’ve explained how to configure the LiteSpeed Cache for WordPress. You can use that tutorial later. For a start, here we’ll concentrate on database optimisation.

Go to LiteSpeed’s Database options, scroll to the “Database Table Engine Converter,” and if you see “Convert to InnoDB” next to any Table, click on it (do it for every table).

You should see this when it’s all done (or right from the start):

After you sort that out, you can scroll up to the “Database Optimizer” and click on the “Clean All” option:

That’s it as far as LiteSpeed is concerned. You’re done. 🙂 Want some more speed and optimisation? Read on.

– T.O.C. –

3. Advanced Database Cleaner Pro optimisation

Back up your database before doing anything!
(I know I’m being repetitive, but my tech. support experience says this warning is not repeated often enough)

This plugin is very novice-friendly and lets you remove all the garbage from your database. When you uninstall a plugin, it often leaves a lot of its info in the database. Over years, this can add up.

There is the free version of this plugin – Advanced Database Cleaner (wp.org link). However, it is also sold with lifetime support for unlimited websites, as – Advanced Database Cleaner Pro (my affiliate link). I did pay the $150 years ago and consider it a great investment. It is very novice-friendly, and I’ve never had any problems using it.

Install and activate the plugin (how to install a premium WordPress plugin).

Go to its options. The “General clean-up” section basically does the same as the above-explained LiteSpeed database optimisation (chapter 2). What we wish to “play with” are the “Tables,” “Options,” and “Cron jobs” sections. This picture explains it better than words (don’t jump to doing that now, read this entire chapter):

You generally want to deal with the unused stuff (“Orphans”).

Before you delete anything, check if any of the options (or tables) are used and/or belong to a plugin, but are just not recognized by the DB cleaner (depending on how well a plugin is written, it may not “announce” everything it owns and uses). For those, select the “Edit categorization” option and assign them to the matching plugin. Here’s an example:

If you aren’t sure about some of the items, you usually can delete them all (without the site breaking – except if you delete any needed Tables). Plugins will re-create the items they need. So if you see some items returning after deletion (when you repeat the scan – as shown in picture 4 – arrow 3), figure out which plugin to assign them to.

Assigning an item to a plugin (or theme) means it won’t be marked as an orphan until that plugin/theme is uninstalled. That’s why it’s important to assign all the items properly. For this example, if I had assigned all the LiteSpeed options to another plugin, they would not get re-marked as Orphans if I uninstall LiteSpeed (and they would become just needless garbage in that case).

The Advanced Database Cleaner can also optimise tables (though we’ll get more into that in chapter 4). Here’s how:

Now, would you like to install a race-car engine in your WordPress database? Read on… 🙂

– T.O.C. –

4. Index WP MySQL For Speed

Back up your database before doing anything!
(repeating again, in case you used the T.O.C. to skip straight to this part 🙂 )

An optimisation plugin which looks to be a result of pure enthusiasm – Index WP MySQL For Speed (WP.org link).

Why would you use it? Because WordPress is not the champion of optimally indexed database tables. This plugin will add some indexes that make table search a lot faster. Yes, it will increase your database size (perhaps even double it). But with relational databases, it does boil down to performance VS (small) size – you can’t have both.

You needn’t keep it installed after the optimisation has been performed, though it doesn’t hurt to keep it and re-index your databases as your website changes and grows. The plugin has the option to monitor your website’s behaviour and suggest optimal re-indexing based on those.

This plugin can also, just like the LiteSpeed Cache plugin (see chapter 2), convert your tables to InnoDB if they are still using MyISAM.

To start, install & activate the plugin, then configure it to monitor your website (depending on the site’s load and traffic, 5 to 25 minutes should do. Here, I’ve configured it to capture everything for 5 minutes – monitoring both the Dashboard (back-end) and the Site (front-end), capturing all the querries (100%):

After the monitoring has been finished, I’ll see a “monitor” tab (as shown in the picture above) with a list of all the queries. You can save it as a CSV file for further analysis by experts.

Next, to optimise your tables, go to the “High-Performance Keys” tab:

If your database is huge, it makes sense to use WP CLI to make these changes and to do it at a time of lower website traffic. Just as importantly, you can always revert back to WordPress standard setup if you aren’t happy with the results:

Unanswered question:
I haven’t been able to reliably test and confirm how this optimisation “stacks” with table optimisations done by the Advanced Database Cleaner plugin (see picture 6). As far as I can tell, they don’t seem to “get in each other’s way” so to speak.

At the time of writing, I don’t have a lot of experience with this particular plugin. It did work fine on staging and on one of my sites. I’m running it on one site at a time and testing. I plan to update this article over the following months (and years).

– T.O.C. –

5. Conclusion?

Database optimisation is not a set-and-forget thing. Not even on the website level (nor at the hosting server level).

Creating backups before touching anything and thoroughly testing afterwards is important. WordPress is great, but that is one of its downsides: updates, plugin changes and problems, database problems etc.

This article is not intended to be a complete optimisation guide (though you can see my optimisation articles, and WordPress optimisation series). Particular websites need different approaches. However, I must say that the above-listed plugins have helped for every website I tried them with, and I’m yet to face any problems with their DB-related optimisations (I did experience some LiteSpeed caching hiccups on occasion, but that’s a different matter).

– T.O.C. –