Database is the powerful standard to keep, serve and alter your data. It is the 'brain' of your website where everything is stored: content, user comments along with the site settings, used themes and plugins. With every change you're making on the website (posting a new article, adding one more gallery, setting up a new theme etc) - its database is updated with a new data as well, rapidly increasing in size. At the same time, a growing number of simultaneous visitors surfing your website and requesting the content leads to the growing amount of basic and complex MySQL queries to the database.
Basic MySQL query is querying data from a single table in database, complex MySQL query - querying data from the multiple tables.
What ends up happening is that finally database becomes overly fat and cluttered with the data which takes up space, increases load on the server and turns out into unimpressive website performance and a visits loss.
That's why we've created this guide to provide you with the easy to follow instructions on Wordpress database optimization which you can start implementing right away! Hope, you'll find it valuable and become one step closer to a fast loading and a snappy website after reading this article.
Let's start with the basic steps on database optimization. Namely, data tables optimization.
This Wordpress optimization plugin is the most simple yet one of the most effective tools to optimize your Wordpress website databases. The only thing you need to know is how to install plugins in WordPress.
Before moving forward, make sure you've created a complete backup of your current database. You can make a backup copy with WP Database Backup or WP-DB-Backup, in the Control Panel of your server or just ask support to do this.
Having said that, let's see what are the steps you need to take to optimize your WordPress database and remove clutter from it.
As a result, you'll see that WP-Optimize has cleaned all stale data (trashed, unapproved, spam comments etc), all post revisions and whitespaces in the data tables.
What's more, this plugin doesn't require much space and you can deactivate it after optimization is finished.
One more plugin for data tables optimization from the author of popular WordPress plugins - Lester Charn..
Its interface is quite simple and intuitive in use. Here you can see at once the detailed summary regarding current volume of trash in the data tables. Furthermore, WP-Sweep allows you to run complete optimization as well as incremental one.
After installation, you can access WP-Sweep by navigating to the Tools (in the left-side bar of your WP Admin panel) -> Sweep.
This option is possible only in case you're using control panel (e.g. cPanel) and have the access to phpMyAdmin tool.
Before modifying anything in the database, make its backup copy.
Now, when the backup copy is created, you are ready to run optimization.
Let’s take the following steps:
define('DB_NAME'). The actual name of your database will follow the
'DB_NAME'part of the code. This is the database you want to use in
As soon as you click this control, phpMyAdmin will run table optimization.
What happens in a result? phpMyAdmin tool runs the same SQL statement OPTIMIZE TABLE with the mentioned plugins in the previous examples. It reorganizes the physical storage of table data and associated index data in order to reduce storage space and improve I/O efficiency when accessing the table.
Defragmenting tables is required when the database is regularly updated in order to remove all whitespaces in the tables and, therefore, accelerate selecting data from them.
The alternative to using special plugins is cleaning database tables manually. Here's some examples of basic SQL statements to get you going.
Bulk delete post revisions:
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
WHERE a.post_type = 'revision'
AND d.taxonomy != 'link_category';
Bulk delete spam comments:
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Bulk delete unaccepted comments:
DELETE from wp_comments WHERE comment_approved = '0';
Mass delete of unused tags:
DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);
Mass delete transients:
DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');
Further optimization belongs to MySQL database tweaking and starts with defining and searching slow queries directly in MySQL.
What you get with detecting slow queries? For example, this helps you to detect the plugin which uses slow, 'heavy' statements and therefore slows down the database and decrease your website performance.
At this stage, basic skills in web-server administration are required.
MySQL slow queries are the queries which take a long time to execute and therefore are the candidates for the further optimization.
To determine slow queries you need to activate MySQL slow query log on your server which is disabled by default. This can be done by setting up the configuration file which is stored, as a rule in
my.cnf by path in
Open this configuration file in any text editor and write the following:
slow_query_log = /var/log/mysql/mysql-wp-slow.log
long_query_time = 5,
/var/log/mysql/mysql-wp-slow.log – is the path to the file where all slow queries will be stored (you need to create this file first, before setting up my.cnf), 5 - is a default value of time in seconds when the statement is executed, any query that takes longer than 5 seconds to run will be considered as slow.
After MySQL restart all slow queries will be saved to the file
/var/log/mysql/mysql-wp-slow.log for their further optimization.
The described above methods of website performance optimization are the basic recommendations only. In every case the subject of optimization may differ regarding to the type of bottlenecks, which kill performance and your business bottom line.