WordPress – Why and How To Optimise the MySQL Database

Since ancient antiquity, humanity has always been forced to come up with ingenious ways of data storage. Data, or rather more specifically knowledge, is crucial to our existence. Be it speculations or facts, it’s always been vital we come up with efficient ways of storage.

It’s easy for us to undermine, especially considering the recent technological advancements of the last few centuries, the remarkable forms in which ancient civilisations used for storing data. From incised tablets to notched sticks to knotted animal hair. Scientists believe images carved in stones is among the earliest form of information storage. The Lascaux cave for instance is thought to be around 20,000 years old. We may struggle to wrap our heads around them but these were technological revolutions of their time.

The Oxford dictionary defines literacy as the ability to read or write. As late as 2011, according to UNESCO Institute of Statistics, the global literacy rate was 84.1% for adults and around 89% for youths. That means approximately 86% of people are literate. Our version of literacy is taken quite seriously in modern times as it enables us to store and retrieve information efficiently, at least with the current level of civilisation.

That ability is measured with relation to alphabets. Yet, in the ancient times, Egyptians picture wrote without any need for alphabets. The Mesopotamians, 5000 years ago, stored information in cuneiforms, basically clay tablets. Clearly, this shows how data storage had been taken seriously even in the ancient antiquity.

By definition, a database is a collection of information that’s organised for efficient retrieval. It can also be claimed then, that the ancient forms of data storage were some form of database, maybe physical databases. Take note of ‘efficient retrieval’ in our definition. That’s one of the reasons we use the particular storage form in the first place.

A modern Content Management System like WordPress definitely has some form of a database at its core. In this case it’s actually a MySQL database, arguably the most popular RDBMS (Relational Database Management System) in the world. It’s powerful. It’s fast. It’s free.

WordPress currently powers about 27% of the web, according to its official website. That’s a huge number. It’s therefore important we have the databases powering these and other sites running at maximum efficiency. History teaches us that not all data storage techniques persist among civilisations, generally because of inefficiency. Technological breakthroughs generally revolutionise our ways of life because of the efficiency they help us achieve. Let’s therefore start getting this efficiency at a lower level, by optimising our MySQL database that powers our Wordpress site.

Objective of this Lesson

• To optimise our WordPress site’s MySQL database for optimal performance. This then improves our site’s load speed, which then allows a better Search Engine ranking. We then get more traffic. Our users end up happy with our site and they come back. We also manage to free more space in our database.

Pre-requisites

• A working wordpress installation. You can have it in either local or remote host. You may also require internet to install one plugin a few Kilobytes in sise.

Advantages of the WP-OptimisePplugin

We are using WP-Optimise plugin over others. This is why:

1. They are the self-proclaimed ‘Best WordPress Database Optimisation plugin’. However, statistics never lie. It has over 600,000 installs as of now. Moreover 608 folks have given it an average of 5/5 star rating.
2. It’s free and easy to use.
3. It receives regular updates.
4. It has a lot of features.

Why Optimisation Is Important

Why then are we insisting on optimising your database? If anything, your database has probably been running for years and you haven’t experienced any catastrophe. Well:

1. It’s more or like your store or garage. It won’t clean itself automatically. You have to clean it yourself. Sure, you may not die if you don’t, however, working in it will be inefficient because of so much unnecessary garbage.
2. Don’t you always edit and update your posts, custom post types and pages? Well, every time you do so, WordPress automatically creates the post’s/page’s revision. Of course it’s also stored in the database. Make 10 revisions and there you have 9 idle lying revisions, comfortably seated in your table, eating up precious space. I’d rather get rid of them.
3. Yes, those spam and trashed comments. Why don’t we just get rid of them?
4. It allows shrinking database as well to get rid of wasted spaces.

Installation

Let’s therefore install our WP-Optimise plugin:

1. Head over to Wordpress plugin’s repository. Search ‘WP-Optimise’. HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/472215801.png" alt="Install WP_optimise" />
2. Click ‘Install Now’ button.
3. Click ‘Activate’ button to activate the installed plugin.

Our plugin is now installed and activated.

Configuration/Settings

Let’s configure our settings:

• Click the ‘Settings’ button under the installed plugin. HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/859174196.png" alt="Plugin Settings" />

• You can see our Options Page being presented to us. We can choose to run the optimisation. First we enable/disable various options according to our requirement. We can choose to: clean all post revisions, remove spam comments, auto-drafts, empty trash, remove pingbacks and trackbacks, etc.

HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/1752642989.png" alt="Options Page" />

• Note the warning. Some of the operations like removing pingbacks and trackbacks among others are intensive. Before any database optimisation attempt it’s always important to back up data. Yes back up data. It’s not a joke. Manipulating database the way we do by optimising it is a sensitive operation and can crash your database however good the code is written. Backups must take place prior to any of these operations.

Here is a simple lesson of how to backup posts by exporting them.

HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/1096451580.png" alt="Warning Page" />

• We also have Table Information tab that shows you the tables you have in your database. HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/1692212196.png" alt="Table InformationTab" />

• Then we have the Settings tab containing settings like scheduled clean ups. HP_NO_IMG/data/uploads/users/a4c0adcd-8650-4b65-9b01-9dd628e70757/1860873391.png" alt="Settings Tab" />

That's it. Just by clicking the 'Run allp optimisations button' in the WP-Optimise tab, we are able to optimise our database for optimal performance. To export your posts before optimising, you can check this earlier tutorial.

Conclusion

Humanity would be nothing without the massive knowledge we have acquired since time immemorial. Knowledge and intelligence gives us an edge over other creatures. Our brains, like many systems are limited in the storage capacity. That’s why we build databases. Ancient civilisations used physical databases like clay tablets for storage. Don’t be fooled, these forms of storage were hot during those days. They were technological revolutions.

However, we’ve since advanced. We are using digital databases. MySQL is one of those. It powers WordPress. It’s then important we optimise our database for optimal performance. It’s what we’ve covered in this lesson. We’ve used WP-Optimise plugin. There a lot of things we do like editing content that create revisions and these end up bloating the database. Cleaning our database is therefore something we must embrace as it improves efficiency.

However, we should never forget to backup our data before optimising the database. In an earlier tutorial right here, we looked at a basic backup mechanism of exporting and importing posts. So no excuses, backup data, then optimise your database. Because a database should always be efficient.