Like any dynamic web, application WordPress relies heavily on its database. Each action performed in WordPress is connected directly or indirectly with its database. From themes to plugins and comments to posts everything uses a database to perform its desired functionality.
Over the course of time, tables inside your WordPress database might clutter and create junk which occupies memory and causes performance issues. This requires frequent WordPress database optimization and cleanup to keep it working smoothly. Consider it as a warehouse where everything is well sorted in shelves but due to lack of maintenance unnecessary items are being stacked covering the actual product. Now reaching an actual product upon request might consume more time than it is supposed to.
While we keep the front-end of our WordPress site neat and clean by performing various optimization; it is also important to keep the WordPress database optimized so that it responds to queries faster.
In this article, we will explore how the WordPress database is structured and what are the techniques used to optimize WordPress database.
WordPress Database Optimization Guide
WordPress Database Structure
If you have ever logged into your phpMyAdmin panel you must have seen database tables. These tables are linked together to make a relationship and serve the required data when requested by WordPress php files. Being a WordPresser, it is beneficial for you to understand the basic structure of the WordPress database.
As of WordPress version 4.4.2, it has 12 tables inside its database. These tables are used to perform various default functionalities of WordPress site (discussed later). However, this number can vary depending upon the number of themes and plugins installed in your WordPress system. Each plugin and theme create its own tables and it won’t be wrong to say that more the plugins you install more the tables you will have in your database.
Before we jump into further details let’s examine the WordPress database diagram so we could have a basic understanding of how a database works in WordPress.
– wp_users: Whenever you create a user is stored in this table along with its details and status.
– wp_usermeta: Stores meta information of users.
– wp_posts: Custom post types, pages data and post data is stored in this table.
– wp_postmeta: Post meta information store here.
– wp_comments: This tables store comments placed on your site.
– wp_commentmeta: Meta information for comments stored here.
– wp_terms: Tags and categories for both posts and links are stored here.
– wp_termmeta: Meta information for terms stored here.
– wp_term_relationships: This stores the relationship between the members of wp_terms.
– wp_term_texonomy: Description of a category, link or tag used in a wp_term table stored here.
– wp_links: This stores blogroll links which are depreciated but can be activated using Link Manager.
– wp_options: Options set in admin are stored here.
WordPress database can be optimized using two approaches. Follow the rest of the article to learn these ways and apply whichever you find convenient.
Advantages of Optimizing WordPress Database
WordPress MySQL optimization has its advantages.
Less Clutter More Space
When unwanted data and tables are removed within the database, things get more organized and less cluttered. This also gives you additional space in your valuable database memory which can be utilized for more productive tasks in future.
Well managed and clean database contribute to overall performance. Queries run through the database fetch data quicker and enhance the overall response time of your website.
Enhanced User Experience
WordPress optimized database improves the performance and speed of the website which ultimately leads to the enhanced user experience. Users may find your site more responsive.
WordPress Database Optimization Using phpMyAdmin
phpMyAdmin is can be used to create, alter and delete databases for your php applications. This tool can be accessed from the cPanel under ‘Databases’.
Since there is no cPanel on Cloudways, phpMyAdmin can be accessed from the application dashboard.
This will open the database manager window listing all the tables and options.
On the left corner, it has an option for inserting SQL commands, Importing and Exporting database and for creating a new table. You can follow along even if you use phpMyAdmin as the options work in a similar fashion on both the tools.
I am using default WordPress installation my prefix in all the commands below will be wp_. Your prefix might be different depending on your setup so make sure to replace it accordingly.
Database Table Optimization
Let’s start with optimizing the tables by running a simple SQL command. This can help in optimizing the tables affected by overhead. For example, if you want to optimize posts table then run the following command:
OPTIMIZE TABLE 'wp_posts'
Above operation can also be done without writing the command. Inside your Cloudways database manager, there is an option for ‘Optimize’. Select the desired table/tables from the list and click on ‘Optimize’ button.
Cleaning Data of Old Plugins and Posts
Sometimes deleting plugins from the WordPress admin is not enough. Although it is uninstalled from the system but leaves behind useless data which requires cleaning. Similarly, posts data is also present in the same table.
Run the SQL command as:
DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';
Replace ‘META-KEY-NAME’ with your desired value.
Delete Post Revisions
Over the time post revisions add up occupying space inside our table. To delete all the post revisions simply run the following command.
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';
You can also define the number of revisions to be allowed. Simply add the following code inside your wp-config.php file.
define( 'WP_POST_REVISIONS', 2 );
Number ‘2’ inside the code shows the number of revisions allowed.
Remove Spam Comments
Spam comments is a big issue in WordPress. It can be avoided by using a plugin such as Akismet which comes pre-installed with WordPress. To delete all spam comments from the table, go ahead and run the following command.
DELETE FROM wp_comments WHERE comment_approved = 'spam';
Deleting Unused Tags
You might want to get rid of old tags especially when they are not used anymore. Tags are accumulated over the time and the easiest way to get rid of them is to run the following SQL query. This query deletes all the tags which are not in use by any post.
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);
Removing Pingbacks and Trackbacks
Pingbacks and trackbacks in comments can be deleted using the following command.
DELETE FROM wp_comments WHERE comment_type = 'pingback'; DELETE FROM wp_comments WHERE comment_type = 'trackback';
Note: Disable pingback and trackbacks before running the above command.
Unused Plugins and Themes
Most of the WordPress users install and deactivate plugins regularly. These plugins rely on WordPress database and even after getting deactivated they leave junk data behind which requires cleanup. This data is needed in case you decide to reactivate the plugin in future but it’s totally useless if you simply do not want to use it anymore.
Similarly, WordPress themes also store data in the database and it stays inside your tables even when you switch to another theme. Therefore, it is important to track these tables and remove them when necessary either by using phpMyAdmin or the plugin such as WP-DBmanager.
Database Optimization Using Plugins
When it comes to WordPress a lot of complex tasks can easily be done by using a plugin. A number of free and paid WordPress database optimization plugins available in the market.
Today we will explore two best optimization plugins and show you how they can be used for optimize WordPress database.
Breeze is a lightweight high-performance WordPress optimize plugin developed by Cloudways. It is a free plugin and comes pre-installed with WordPress installation in Cloudways.
Once the plugin is installed and activated, go to Breeze → Settings → Database and either Select all or choose the desired option for optimization.
As you can see it contains all the options we discussed earlier in this article. Once you made your selection click on ‘Optimize’ to initiate the process. Once the process is complete it will show the message box stating ‘Database cleanup successful’
WP-Optimize is another WordPress optimize plugin used to optimize database in WordPress. This plugin is widely used and cleans the database effectively and automatically. This plugin can easily be downloaded from the WordPress dashboard.
Optimizing the database is very simple using this plugin. As you can see it also has the list of options for removing unwanted revisions, comments, posts data, and transient options. Make your selection and click on ‘Run optimization’ to initialize the process.
In this article, we discussed WordPress database optimization in detail and saw why it is important to run these cleanups every now and then. We also learned two different ways of optimizing database on a WordPress site. If you find this article useful or would like to add something, please share your feedback.
Start Growing with Cloudways Today!
We never compromise on performance, security, and support.
Ibad Ur Rehman is a WordPress Community Manager at Cloudways. He likes to explore the latest open-source technologies and to interact with different communities. In his free time, he likes to read, watch a series or fly his favorite Cessna 172SP in X Plane 11 flight simulator.