WordPress Database Optimization Tools And Techniques

WordPress Database Optimization Tools And Techniques

Posted on 25 Oct, 2018 by Ajani Ashish | No comment

WordPress Database Optimization Tools And Techniques

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

ERD WordPress Database

Here is an overview of all the tables created while standard WordPress installation.

Table nameDescription
wp_termmetaEach term features information called the meta data and it is stored in wp_termmeta.
wp_commentsThis table store comments posted on WordPress website.
wp_commentmetaThis 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_usersWhenever a new user created, this table stores information about the user.
wp_usermetaThis table stores meta information of every user record.
wp_postsPosts and pages are main features of WordPress and this table stores information for both.
wp_postmetaThis table contains meta information about WordPress pages, posts, and custom post types.
wp_termsCategories and tags for both posts and links are stored in this table.
wp_term_texonomyThis table stores the taxonomy including category, link, or tag for the entries in the wp_terms table.
wp_term_relationshipsThis table stores the relationship between the members of wp_terms.
wp_linksTo store and manage blog rolls created by earlier versions of WordPress or the Link Manager plugin.
wp_optionsThis 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.

wp_ prefix
wp_ prefix

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.

Optimize Tables
Optimize 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 Plugin

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 Plugin

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 Plugin

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

Optimize Database after Deleting Revisions
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

WP-Sweep - WordPress Database Optimization Plugin
WP-Sweep – WordPress Database Optimization Plugin

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.

Related Post

WP Copy Media URL an excellent utility plugin

Posted on 28 Mar, 2018 by Ajani Ashish

While dealing with large content we often need to copy media URLs from media library and here WP Copy Media URL plugin helps a lot. With my personal experience I often need to copy media URLs while migrating content...

Read More

WordPress 4.8.3 security release

Posted on 1 Nov, 2017 by Ajani Ashish

WordPress 4.8.3 security release is available now. Because of security release I strongly recommend to upgrade WordPress version of your website immediately. In earlier versions of WordPress upto 4.8.2, $wpdb->prepare()...

Read More
No Comments (0)

Speak Your Mind

Your email address will not be published. Required fields are marked *