Speed Up Your WordPress Site Easily: Database Optimization

How to optimize Wordpress database with WP-Optimize plugin

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.

Having said that, let's see what are the steps you need to take to optimize your WordPress database and remove clutter from it.

1. Install and activate WP-Optimize.
2. Navigate to the plugin settings by clicking on the menu item labeled WP-Optimize in your WordPress Admin bar.
3. In the list of operations check the items, which you want the plugin to optimize in your database. Items marked in red color headline advanced database tuning. Check them only in case you’ve got a fresh backup copy and know how to restore database from backup.
4. Press on Run all selected optimizations button.

You’re done!

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.

How to optimize Wordpress database with WP-Sweep

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.

How to optimize Wordpress database in phpMyAdmin

This option is possible only in case you're using control panel (e.g. cPanel) and have the access to phpMyAdmin tool.

Now, when the backup copy is created, you are ready to run optimization.

Let’s take the following steps:

1. Log in to the phpMyAdmin panel and then select the database which corresponds with your WordPress website in the left-side menu. If you don't know which database you're using for a specific WordPress website, you can find that by accessing your wp-config.php file - it is located in the root folder. Open this file in any editor and find the row which starts with 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 phpMyAdmin.
2. Check the names of datatables which you want to optimize. If you want to optimize all of the tables - scroll to the bottom of the table list and click on the link «Check all». On the right side to «Check All» command, you’ll see a drop down menu «With selected»- open it and choose item «Optimize Table»:

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.

Useful SQL-statements to clean your Wordpress database

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:

DELETE a,b,c
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.

Advanced Steps on MySQL Performance Tuning: How to find MySQL slow queries?

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 etc/mysql/my.cnf or etc/my.cnf.

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,
where /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 Takeaway
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.