Community
2 15223
HostiServer
2018-11-06 14:22

Speed Up Your WordPress Site Easily: Database Optimization

Basic Steps On MySQL Optimization (WP Optimize, WP Sweep, phpMyAdmin)

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.

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.

Caution!
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.

  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.
Optimize Wordpress database with WP-Optimize

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.

Optimize MySQL with WP-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.

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:

  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»:
  3. Optiizing tables in phpMyAdmin

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.

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?

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 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.

Contents

MANAGED VPS STARTING AT

$19 95 / mo

NEW INTEL XEON BASED SERVERS

$100 / mo

CDN STARTING AT

$20 / mo

 

By using this website you consent to the use of cookies in accordance with our privacy and cookie policy.