Every WordPress installation uses one single database where all of your site content is stored, such as posts, pages, comments as well as custom post types.
However, the database maintenance is often neglected by most webmasters, and many unnecessary data like spam comments and older post revisions pile up over time.
In this guide, we will explain what can be done to improve the efficiency of your WordPress database. To start, let’s have a quick understanding of the WordPress database, along with top reasons to optimize it.
What Does WordPress Database Contain & Why You Should Optimize It?
This section will give a quick understanding of the core database tables of WordPress. Usually, a fresh install consists of 11 core tables, and a website installed with plugins and themes probably contains more. When checking your WordPress database, you may notice the tables listed below. Note that, there are many other tables generated by a WordPress plugin or theme.
- wp_users is where to list all registered users on your website.
- wp_comments is where to contain all the comments on your website.
- wp_options is where to display the options that are defined within the admin area.
- wp_posts is where to store posts, pages, navigation menu items and other custom post types.
- wp_term_relationship is where to display the association between link and link categories, and the association between categories and tags.
WordPress always accumulates many kinds of information over time, and drafts, spam comments, post revisions and data from uninstalled plugins will increase its database size undoubtedly. A large database can dramatically affect the website performance as it takes much time for webserver to find and retrieve information from the tables. Besides, the bigger the database, the longer it takes for backing up and transferring your website.
What Are Included in the Database Optimization?
Simply put, optimizing or cleaning up your database means discarding the data and information that take up much space of database and are not nec
Deleting overhead
Usually there are two columns included in your database – size and overhead. The table size largely depends on the amount of data that is stored, and it increases along with the number of rows. Overhead refers to temporary disk space used by your database to save queries, and it will increase in size gradually. Optimizing your database will delete the overhead so that the overall size of the database will be reduced.
Removing Data Bloat
Most WordPress databases accumulate unnecessary data, and this additional bloat is what makes your website slow. There are many things that add bloat to the database, and below are some common examples:
- Spam comments. Although spam comments will be automatically deleted after 30 days, they still take up a lot of space within your “wp_comments” table during that time. This WPCommentCleaner lets you bulk delete spam, unapproved and approved comments.
- Unused tables for plugins and themes. Both WordPress plugins and themes store settings in the website database, and some of these settings won’t be removed even though you uninstall a plugin or switch a theme.
- Post revisions. That’s a useful feature that lets you revert to the older copies of posts. As WordPress has no limits on the number of revisions, this fail-safe system may take up dozens or even hundreds of rows in the website database. There is also a useful plugin named Better Delete Revision to remove the unused post revisions and optimize your database.
- Deleted items. Each time when you delete an item in WordPress, it will be sent to the trash folder. This prevents you from deleting posts, images or links by accident. If you delete a number of items regularly, those deleted items will still eat up your space.
How to Optimize the WordPress Database with a Plugin?
It’s possible to use phpMyAdmin and the wp-config.php file to optimize the WordPress database, which however requires you write SQL commands and also understand the ins and outs of MySQL. If you want a cleanup tool that removes all the unneeded data at the touch of a button, then this WP-Optimize plugin is your choice.
Plugin URL: https://wordpress.org/plugins/wp-optimize/
Step 1: Log onto your WordPress dashboard and search for this WP-Optimize plugin via “Plugins” > “Add New”. Just click the “Install Now” button and follow the prompts to finish activating it.
Step 2: Bring your mouse over this “WP-Optimize” section to further modify the optimization progress. Here, just check the boxes for particular “Clean-up Options” and then hit this “Progress” button.
Step 3: Meanwhile, you can schedule the clean-up and optimization process via “Settings” > “Auto Clean-up Settings”. The number of weeks data is also editable via the “General Settings” section. Do remember to save settings at the end.
