Currently, 11 core tables are created during the standard installation of WordPress and some other tables are gets created from plugins & themes also. Your WordPress site data such as posts, pages, comments, custom post type, media, plugin & theme settings and core WordPress settings stored in these tables.
Every database will require some form of maintenance to keep it at an optimal performance level. The database grows over time with unnecessary data and overhead if you are updating your site regularly. Unnecessary data such as Spam comments, post revisions, drafts, and uninstalled plugins data increases its size.
A large and unoptimized database can affect the performance of your site load time and it may crash abruptly. This is because the server takes quite longer than usual time to fetch the data from it. Backing up bigger database eats up server process/memory and seriously affects migration process in case if you would like to move to another web host.
You can improve performance, stability of WordPres database by housekeeping the unwanted data regularly. Let us look at the options available for database optimization.
WordPress Database Topology
Here is the overview of all the 11 tables created during WordPress standard installation. If there are any tables present other than listed below were created by your plugin or theme.
- wp_posts – Stores posts, pages, menu items and custom post types data
- wp_postmeta – Stores meta data of posts, pages and custom post types
- wp_terms – Stores categories and tags for posts/custom post types
- wp_term_relationships – Posts association with categories & tags are maintained in this table
- wp_term_taxonomy – Stores description/taxonomy of the categories, tags, links present in wp_terms table
- wp_comments – Stores all comments
- wp_commentmeta – Stores meta data of your comments
- wp_users – Stores all the registered users
- wp_usermeta – Stores additional information of each user
- wp_options – Stores option settings availabe in admin area
- wp_links – Stores links entered into Links feature – This has been deprecated since WordPress 3.5 but can be re-enabled with Links Manager plugin
What type of data that is safe to remove ?
You can safely remove the data that you no longer need or is only taking up your database space or the data that is not required to run your WordPress site.
Remove Overhead
Overhead in a database usually created after several updates or deletes to a table with variable-length rows. It is temporary space to run the queries and comparable to defragmentation in a hard drive. So, it gets build up over time and need to keep tuned up regularly for optimal performance.
Overhead in database tables can be removed using WordPress plugins and manually via phpMyAdmin.
Remove Bloat
Data bloat is nothing but unwanted data that is not required to run your WordPress site. The WordPress database gets filled up with data bloat over time that just taking up precious space. Data bloat can be removed via plugins and manually using useful code snippets.
Some of the data bloats include
- Post revisions
- Spam comments
- Orphan postmeta, commentmeta and term relationships
- Orphan data from deleted plugins and themes
- WordPress Transients
- Deleted items
Way to optimize WordPress database
Let's take a complete backup of your WordPress database before we start with the optimization steps. This way you can secure your database in case of any mess. This is very important and don't skip this part.
Database optimization using Plugins
Database optimization can be done using plugins available in the WordPress repository. You get easy options in the admin dashboard to remove the unwanted data selectively. These plugins are very useful for the beginner who don't want to risk the site operation by accessing database tables directly from phpMyAdmin.
WP-Optimize
Simple yet most popular plugin to effectively clean up and optimize your WordPress database without requiring PhpMyAdmin access. Easily optimize database by cleaning up spam & unapproved comments, trash, trackbacks, pingbacks, expired transient options and lot more.
You can optimize the database with just a click of the button. The plugin will list all the tables in the database, space each one taking up and overhead space if any. It is possible to schedule automatic cleanup of the database in regular interval.
WP-DBManager
WP-DBManager is an advanced plugin available in the WordPress directory. Apart from optimization, it also provides other options like backup, repair, drop/empty tables and running database queries directly from the database. This doesn't provide an option to remove bloat from your database but as a one click optimize button to remove overhead.
WP Clean Up
WP Clean Up is similar to WP-Optimize plugin which provides easy to use interface to optimize WordPress database. You can selectively remove each and every bloat stored in your database to free up space.
Database optimization manually
Manual database optimization involves running a SQL queries using PhpMyAdmin interface and adding additonal settings to wp-config.php configuration file. You can also use plugin like WP-DBManager to run SQL queries directly from WordPress dashboard without needing to access PhpMyAdmin.
Make sure to replace the table prefix wp_ if your database is using another prefix
Optimizing tables using phpMyAdmin
PhpMyAdmin is an open source tool to handle administration task of MYSQL databases. You can do various tasks like creating, updating, deleting, optimizing, repair table and loads of other options.
You will need to check the table that needs optimization and select the “Optimize table” option from the drop-down list. This will remove the overhead and optimizes database for you. There are other options like repair, empty/drop table which works much in a similar way.
Post revisions
Post revision is nothing but a version control feature in WordPress. Revision system stores a record of each saved draft and published updates in the post table. It allows you to see what changes were made by comparing post with previous revisions.
By default, WordPress keeps track of all the changes to title, author, content, excerpt in a post. Although it is a great feature that everyone require in case if one would like to go back to the previous version, but it can significantly take up the database space over time.
To tackle this issue, you need to limit the number of post revisions that WordPress stores in the database. Keeping two revisions for an individual post is the optimal setting. Add below line to wp-config.php if you want to limit yourself to two revisions.
define( 'WP_POST_REVISIONS', 2 );
And to disable the post revisions completely one can set the below line.
define( 'WP_POST_REVISIONS', false );
Limiting the post revisions although doesn't delete which are already present in your database. For this, you need to make use of above-listed plugins and also you can delete the revisions manually by running the below delete query. This will delete all the revisions present in your WordPress database.
DELETE FROM wp_posts WHERE post_type = 'revision';
Spam comments
The spam comment is part of a life of every blogger. WordPress provides built-in tools to prevent comment spam and also plugin such as Akismet to provide additional protection. This plugin differentiates which comments are legitimate and which are comment spam.
Even though if we use defense plugin, spammers attack blogs in many ways and submit a huge number of spam comments which takes up huge database size. Use below command to delete all the comment spams from comment table.
DELETE from wp_comments WHERE comment_approved = 'spam';
Orphan postmeta, commentmeta and term relationships
Post meta is an additional data attached to every post. If the posts are deleted often, there is a high chance that some of the redundant data will leave behind. Similar to this, redundant data for comment meta data and term relationships are also can be deleted safely.
Command to delete redundant post meta:-
DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL
Command to delete redundant comment meta:-
DELETE FROM wp_commentmeta
WHERE comment_id NOT IN (
SELECT comment_id FROM wp_comments
);
Command to delete redundant term relationships:-
DELETE tr FROM wp_term_relationships tr
LEFT JOIN wp_posts wp ON tr.object_id = wp.ID
WHERE wp.ID is NULL;
Trash items
Trash is a place to put unwanted things. Every deleted item(posts, pages, comments etc) are sent to the trash before deleting them permanently. By default, once the item is placed into the trash, it remains there for 30 days.
You can reduce the days after which the trash items will get deleted permanently from the trash bin. All you need to do is to add below code in the wp-config.php file.
define('EMPTY_TRASH_DAYS', 5);
If you would like to disable the trash functionality, set the number to 0. The ‘Delete Permanently' button will appear instead of ‘Trash' button.
Unused tables
Many themes and plugins do not come up with clean uninstall steps and they do not clean up their tables after you uninstall them. What happens most of the time is that the corresponding files & folders are get deleted but not the tables associated with them. This leave some tables leave behind in your database.
There is a WPDBSpringClean plugin for your rescue. This plugin scans your WordPress install and finds out unused database tables and then gives you the option of deleting them with a single click.
