Main aim of WordPress Database Optimization is to improve website performance by removing unwanted data and reducing the database size. The process of optimizing WordPress database is not hard. In this article I will describe how to optimize WordPress database easily.
WordPress Database Optimization process is as much important as optimizing the front-end of the website. This helps in improving database performance by responding faster to database queries. A new website with default WordPress setup is having comment set of tables. As soon as we start installing themes and plugins, number of database tables keep on rising and the database keeps on getting larger. Even if we remove or deactivate some WordPress plugins, it does not remove tables from database. Once the database grown larger it requires optimization and cleanups to keep the website functioning smoothly.
Note: Before jumping into the list I strongly recommend to take backup as these plugins will work with database. You can not undo the database changes so it’s better to be safe and secure upfront.
Structure Of WordPress Database
WordPress is using MySQL to store dynamic content along with users information, website settings and other website information. At this time the current WordPress version is 4.9.8 and it comes with 12 default tables in database. This number will grow larger as soon as you start installing new themes and plugins as every theme and plugin may create new tables into the database.
WordPress default database structure
Here is an overview of all the tables created while standard WordPress installation.
| Table name | Description |
|---|---|
| wp_termmeta | Each term features information called the meta data and it is stored in wp_termmeta. |
| wp_comments | This table store comments posted on WordPress website. |
| wp_commentmeta | This table consists 4 fields including meta_id, comment_id, meta_key and meta_value. It is to store meta information about comments posted. Here comment_id field relates to wp_comments table. |
| wp_users | Whenever a new user created, this table stores information about the user. |
| wp_usermeta | This table stores meta information of every user record. |
| wp_posts | Posts and pages are main features of WordPress and this table stores information for both. |
| wp_postmeta | This table contains meta information about WordPress pages, posts, and custom post types. |
| wp_terms | Categories and tags for both posts and links are stored in this table. |
| wp_term_texonomy | This table stores the taxonomy including category, link, or tag for the entries in the wp_terms table. |
| wp_term_relationships | This table stores the relationship between the members of wp_terms. |
| wp_links | To store and manage blog rolls created by earlier versions of WordPress or the Link Manager plugin. |
| wp_options | This table stores settings information. |
The prefix “wp_” before each table name is the default database prefix, it can be different if it is changed while installing WordPress.
Use phpMyAdmin For WordPress Database Optimization
phpMyAdmin(https://www.phpmyadmin.net/) is an open source tool which helps creating, editing, managing and deleting databases for a website. It is a graphical interface which helps managing MySQL database.
After using WordPress for a while database of your website becomes fragmented. It will increase the query execution time because of memory overheads. You can optimize the database using single command or say by following few steps from phpMyAdmin.
These are steps to optimize database tables:
- Go to phpMyAdmin
- Select the database you like to optimize
- In table listing page, click on “Check All” to select all tables
- From “With Selected” drop down, choose Optimize table
Database tables will be optimized by de-fragmenting tables.
Few configuration options to remove unnecessary data
Alongside with WordPress Database Optimization through phpMyAdmin, you can also optimize the database by changing constant values in WordPress configuration file (wp-config.php).
- Auto-saves
WordPress saves pages and posts automatically after specified duration. It means whenever you are writing article WordPress auto-saves it after number of seconds specified. You can add the following line in wp-config.php with desired interval to increase the duration.
define( ‘AUTOSAVE_INTERVAL’, 300); - Empty Trash
WordPress by default stores deleted/removed items for 30 days in database. You can add following line in wp-config.php with less number of days to empty the trash automatically. This will help in decreasing query execution time.
define( ‘EMPTY_TRASH_DAYS’, 5); - Number Of Revisions
One of the most useful feature of WordPress is revisions. It helps blogger in loading a previous version of the article in case of disaster. Although there is no limit on how many revisions can be stored in database, it can be 5, 10 or even 30. You can limit the number of revisions by placing following line in wp-config.php
define( ‘WP_POST_REVISIONS’, 5);
Best WordPress Database Optimization Plugins
Database optimization process seems bit complex and time consuming process but number of free plugins available in market can make this process pretty much easier. With ready-made WordPress plugins, you don’t have to manually go through database tables. Additionally, plugins will provide wide range of options which may help in optimizing database very well.
Here is the list of plugins can help in optimizing WordPress database.
WP-DBManager
WP-DBManager – WordPress Database Optimization PluginThis is very popular WordPress plugin having 100,000+ active installs. This plugin allows you to backup database, delete backups, restore backed-up database, optimize and repair database. You can schedule automated backup with this plugin. You can also empty/drop tables and run database queries directly from the interface provided by this plugin.
WP-Optimize
WP-Optimize – WordPress Database Optimization PluginThis is an effective WordPress plugin which helps in automatically cleaning your WordPress database and allows your website to run at maximum efficiency. This is one of the best plugin available for WordPress Database Optimization. This plugin is available in both free as well as premium versions but if you are running a small blog or small business website, free version of the plugin will surely do the job for you. The optimization process with this plugin can be easier and quick to get started optimizing database.
This plugin can remove all unwanted data from the database including track-backs, pingbacks and comments that no longer needed. It can also de-fragment database tables and let you control the optimization with great details.
Note: If tables of your MySQL database are built with InnoDB engine, this plugin may not perform database table optimization. You need to perform operations manually, via phpMyAdmin.
Advanced Database Cleaner
Advanced Database Cleaner – WordPress Database Optimization PluginThis WordPress plugin is not as much popular as other plugins but it is very helpful plugin in cleaning up and maintain WordPress database. With this plugin you can clean up the WordPress database by deleting orphaned items like old drafts, old revisions, etc…
Optimize Database after Deleting Revisions
Another popular plugin is “Optimize Database after Deleting Revisions” having 100,000+ active installs. With just one click you can clean and optimize your WordPress database. Similarly to the other WordPress plugins mentioned in this post, this plugin will delete revisions, expired transients, unnecessary comments, tags, etc… You can faster the database queries after cleaning up database with this plugin.
WP-Sweep
This is another WordPress Database Optimization plugin built by same WordPress developer Lester Chan who built WP-DBManager plugin. This plugin helps in cleaning up revisions, comments, orphaned data, auto drafts, duplicate data, transient options, etc… This plugin uses proper WordPress delete functions as much as possible instead of running direct delete MySQL queries.
Over the period of time, your WordPress website grows larger in terms of content hence the database may have unused records, redundant tables and many unnecessary entries which you can remove without affecting the website. WordPress Database Optimization process is a must do process for every WordPress website to maintain the higher performance and keep the site of the database at a minimum. You can take a look at here to know more about website performance measurement tools.
As an overview, WordPress Database Optimization process is easier if you are using plugin and it can be little bit riskier and time consuming if you are doing it manually. What is your thought on this? Please do let me know what you are thinking about WordPress Database Optimization and which method is best. In case if I missed something, you can share ideas with your comments.
Thank you for this amazing and very helpful blog post. this is truly a gem post for newbies like me thanks again.