Maintaining and optimizing doesn’t magically create an amazing website, from a rudimentary, leisurely website, but it helps achieve it. If you imagine speed optimizing as a large cake, this would be a slice of it.
You don’t have to freak out, if MySQL, the database table and similar expressions sounds Chinese to you, as I will explain everything step by step, what is what, and what you have to do to achieve a fast and maintained database. Let’s get started!
What is a database?
The database, as it is suggested by the name, serves to store data. Content management systems (WordPress, Joomla) wouldn’t work without MySQL database.
The database is made up of tables, in which you find different informations. The WordPress creates 11 tables during installation, and the number of tables grow as you install various plugins. Not only does the number of the tables, but their sizes increase too, but we will get into that later on.
What kind of information does the database store?
In the database information on settings regarding the website and extensions, the user’s data, the entries data and metadata, categories, tags, comments without the requirement to be complete can be found.
As you can see, all the important information can be found in the database. As you start using the website more, install various plugins, create more and more posts, the size of the database starts increasing too.
What else can increase the size of the database?
WordPress provides a very handy function, saving an entry’s or page’s version (post revisions). Every time you are editing an entry and press save, it creates a new version. From this version you can bring back the entry’s previous versions.
This is a very useful function, however, if you have lots of entries and you edit them often, there is a good chance that your database contains versions that you don’t need anymore. And these can increase the size of your database by a good bit.
Why is it important to maintain your database?
Try to imagine your database as your computer’s hardware. When you copy and delete files, and install and uninstall programs, leftover files stay on your computer and your hardware becomes fragmented. If your hardware is fragmented, it takes longer to receive data and your computer becomes slower.
The database behaves the same way. When the website is running it receives information from the database. If the database is too large, because it contains unnecessary data and it is fragmented, then receiving the data will take longer and your website will be slower too.
How can you maintain your database?
It’s important, that before you use any of the maintenance methods, you make a backup of the database. You can do this with the help of PHPMyAdmin, or with the cPanel Full backup function.
The PHPMyAdmin is there to manage the database, therefore one of the maintenance options is running the PHPMyAdmin’s optimizing database function.
The other option, if you are using the WordPress admin interface, is to use an extension to clean out the database and do a defragmentation. For this the WP-Optimize provides a good solution, and now I’m going to demonstrate how to use it.
How to install, configure and use WP-Optimize?
The WP-Optimize is the most popular database optimizing extension on WordPress. It can take pride in the over 800.000 current installation and the 4.9 average rating. When writing this article (30.11.2018) it was updated two weeks ago, as the developers are constantly working on it.
There are two options for installing:
The first one is downloading the extension from the WordPress Plugin Directory, following this link: WP-Optimize, then uploading the package through the Plugins–>Add New by clicking on Upload extension.
The other option is going to the Plugins–>Add New, and write in WP-Optimize on the right hand-side search bar. It’s the first orange coloured icon that you have to install, and then activate.
The WP-Optimize text and icon on the WordPress’ admin interface left hand-side will appear, after the installation and activation, and by clicking on it you can open the extension’s settings.
On the extension’s page there are 4 tabs: WP-Optimize, Table information, Settings, Plugin family.
On the WP-Optimize tab you can see the various actions, the first 6 is automatically ticked, these settings are safe and enough (optimizing database tables, deleting entry edits, removing spam and deleted comments, removing draft and deleted entries, removing not approved comments).
On the right hand-side of this site, you can also see the PHP and MySQL version, the actual settings regarding the maintenance, and the current size of the database.
On the Table information tab you can see the tables that create the database, and the information regarding their sizes and types. You don’t have to do anything with this.
On the Settings tab, there are two sections, the General settings and the Scheduled clean-up settings.
If you are using the General settings, the first option – Keep last 2 weeks data – is ticked, you can set to keep the data from the last two weeks, and delete the previous ones. This number can be edited of course.
This version is optional, I don’t use it myself, but if you would like to keep the last few edits of your entries, than it might be useful to you.
In the Scheduled clean-up settings you can activate WP-Optimize to automatically maintain the database at specified intervals. This is a very comfortable option, but I personally don’t use it. I prefer to run the database’s maintenance myself, after creating a backup.
You don’t have to do anything with the Plugin Family tab, this is technically an advertisement for the extension developers.
When you complete the backup and get to know the possibilities through the extension, there is nothing else left, but optimizing the database. You don’t have to do anything else, other than clicking on the blue buttoned Run all selected optimizations.
The WP-Optimize starts it’s job, and once it is finished on the Status box you can see how much space did the extension free in the database.
