Like any dynamic web application, WordPress relies heavily on a database. Each action performed in WordPress is connected directly or indirectly with its database: from themes to plugins and comments to posts everything.
A database is like 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.
We pay ample attention to the front-end of our websites by choosing the best WordPress hosting, themes, plugins, etc. The similar attention is needed to our back-end by optimizing our WordPress database for faster response times.
WordPress Database Structure Explained
When you log in to your phpMyAdmin panel, you see database tables. These tables are linked together to serve the required data when requested by WordPress .php files. As a WordPress user, it is useful for you to understand the basic structure of the WordPress database.
From version 4.4.2 onwards, WordPress has 12 tables inside the database. These tables are used to perform various default functionalities of WordPress. However, this number can vary depending upon the number of themes and plugins installed in your WordPress.
Each plugin and theme create its own tables and it won’t be wrong to say that more the plugins you install the more tables you will have in your database. Let’s see the WordPress Database Diagram, so we can have a basic understanding of how a database works in WordPress.
– WordPress 4.4.2 Database Diagram
Let’s take a look at what each database table stores:
- 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 are stored in this table.
- wp_postmeta: Post meta information store here.
- wp_comments: This table stores 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 that are depreciated but can be activated using Link Manager.
- wp_options: Options set in admin are stored here.
Why Should You Optimize 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 the future.
A well-managed and clean database contributes to overall performance. Queries run through the database fetch data quicker and enhance the overall response time of your WordPress site.
Improved User Experience
WordPress optimized database improves the performance and speed of your site, which eventually leads to an improved user experience. Users may find your site more responsive.
Method 1: Optimize WordPress Database Using phpMyAdmin
phpMyAdmin can be used to create, alter and delete databases for your php-based applications. It can be accessed from the cPanel under Databases.
– phpMyAdmin in cPanel
If you’re a Cloudways user, you can access phpMyAdmin from Application Management.
– MySQL Access in Cloudways Platform
This will open the database manager window listing all the tables and options of your WordPress.
– Database Manager in Cloudways Platform
On the left corner, it has an option for SQL Command, Import, Export, and Create Table. If you want to create a table in the database, you can do it with the help of SQL commands as well as with a UI option, same is the case for import and export.
I use default WordPress and 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. It can help in optimizing the tables affected by overhead. For example, if you want to optimize the posts table, then run the following command:
OPTIMIZE TABLE 'wp_posts'
The above operation can also be done without writing the command. Go to Cloudways Database Manager, there is an option for optimization. Select the desired table/tables from the list and click on the Optimize button.
– Optimize WordPress Database on Cloudways
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 useless data which requires cleaning. Similarly, the 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
Post revisions add up occupying space inside your table. To delete all the post revisions, 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. For that, 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 are a big issue in WordPress. You can moderate them 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';
Delete Unused Tags
You might want to get rid of old tags, especially when they are not in use anymore. Tags are accumulated over time and the easiest way to delete 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);
Remove 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 users install and deactivate WordPress plugins regularly. These plugins rely on the WordPress database and even after getting deactivated, they leave junk data that requires cleanup. This data is needed if you decide to re-activate the plugin.
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 a plugin such as WP-DBmanager.
Why Speed is Crucial for SEO & UX?
These expert tips will show you how to improve the speed of your WordPress Websites.
Your Ebook is on its Way to Your Inbox.
Method 2: Optimize WordPress Database Using Plugins
When it comes to WordPress, many complex tasks can easily be done by using a plugin. A number of free and paid WordPress database optimization plugins are available online. I recommend the following two plugins that can be used to optimize WordPress databases.
– Breeze WordPress Plugin
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.
– Breeze Database Options
Once the plugin is installed and activated, go to Settings → Breeze → Database Options and either Clean All or choose the desired option for optimization.
– Dabatase Optimization in Breeze
Once you make your selection, click on the Optimize button and it will clean up your WordPress database.
– WP-Optimize WordPress Plugin
WP-Optimize is another WordPress plugin used for database optimization. It cleans up the WordPress database effectively and automatically. You can install and activate this plugin from your WordPress dashboard.
– WP-Optimize Database Options
It also has a list of options for removing unwanted revisions, comments, posts data, and transient options. Make your selection and click on the “Run all selected optimizations” button to initialize the process.
I discussed WordPress database optimization in detail and why it is important to run these cleanups every now and then. You have also learned two different methods of optimizing databases on WordPress. If you find this article useful or would like to add something, please share your feedback in the comments section below.
Q: How Do I Optimize my WordPress Database?
You can optimize your WordPress database by accessing phpMyAdmin via your hosting provider. You will be required to log in to the cPanel, but if your hosting provider does not use a cPanel, look for the alternative MySQL management tool. You may then optimize your database from there. Also, you can use a WordPress database plugin to optimize your database easily.
Q: Is WordPress Good for Database?
Yes, WordPress is good for a database and uses MySQL as its database management system. MySQL is an open-source software used for creating a database and retrieving data when required.
Q: How Does WordPress Store Database Data?
WordPress stores database data in the form of tables. All the unique data is presented in rows in each table. WordPress automatically creates the tables by default as soon as you set up your website.
Q: How Do I Use WordPress to Create a Database?
You can use WordPress to create a database using Plesk, cPanel, phpMyAdmin, or MySQL client.
If you are a Cloudways user, you can create a database via phpMyAdmin by following the steps below:
- Access phpMyAdmin from Application Management.
- You will see the database management window.
- Click Create Table.
- Now set your preferences by either using the SQL commands or via the UI option.
Customer Review at
“Beautifully optimized hosting for WordPress and Magento”
Arda Burak [Agency Owner]
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.