Optimize WordPress Database – The Ultimate Guide

How to Optimize WordPress Database – Lots of people search this query on google as WordPress covers up to 30% of internet’s websites, also many of our subscribers were asking to cover articles on WordPress hence, we decided its essential to start by providing something informative, and, its all about the database!!.

Database is the essential cornerstone of any website, which is used to store the data. Hence considering its importance, it is also essential to take care of the database.

If you own a WordPress website that is updated frequently or after a long period of time, then i guess, you rarely need this ultimate guide. But if you own a website that is updated on daily basis then for you, it is extremely essential to check this guide.

Why you must Optimize WordPress Database?

Your WordPress website stores all kinds of things at once, which includes :-

  1. Blog Posts
  2. Draft Posts
  3. Page Content and their Drafts
  4. Theme Settings
  5. Plugins
  6. Theme Data
  7. Comments
  8. Spam Comments
  9. Image Data
  10. Metadata
  11. Contact Information
  12. Links
  13. Custom Posts Types
  14. Form Entries etc..

And many more things, if you own a blog or website that gets updated on daily basis, then the list of data mentioned above gets multiplied day by day, as a result, it affects your website’s performance and your website may even suffer from down time or crash.

This is something which can be considered as “Important thing” for individual website owners too.

Individual website owners, who hosts their website on smaller servers, can suffer from down times if they do not take care of their database.

The disease of website suffering through the constant crashes and down time is ultimately linked to the point that – “Your server quality is cheap”.

However, everyone forgets or maybe they don’t even know that, if a website gets down or it crashes, then their must be other factors linked to it or than server’s quality or capability.

  1. Heavy theme which is bloated by features
  2. Poorly coded theme
  3. Poorly optimized theme that doesn’t deliver the content properly
  4. Poorly optimized database
  5. Too much traffic on a smaller server
  6. DDOS Attack (Distributed Denial of Service attack) – Which is a hacking attack

If any of the above mentioned thing happens then, your website’s server won’t take the load and it suffers from down time and crashes.

Hence, its essential and important to optimize wordpress database, because by doing this you’re eliminating one of the potential “Risk factor” that might harm or break your wordpress website.

As well as you’re keeping the website in a peak condition.

Note – if you want to avoid the other mentioned risk then you can check out this article – Comprehensive beginner’s guide to web application security.

First you need to Understand Your WordPress Database

Before moving forward with the steps to optimize wordpress database, its essential to understand how the database of wordpress content management system works. Almost, every server in the world, uses “PhpMyAdmin” to manage the wordpress databases.

Typical WordPress Database - Optimize WordPress Database Guide
Typical WordPress Database – Optimize WordPress Database Guide

Note : The screenshot is above is taken from a local wordpress installation, which is installed locally on computer.

If your server has “CPanel” then you will see “PhpMyAdmin” interface shown in the screenshot above.

However, Your hosting plan might also be using the different MySQL management tool other than PhpMyAdmin.

However, remember this that the default 12 tables won’t change.

You can also use some different wordpress plugin to manage and access your database.

However, we usually don’t advise our clients to do that, because using a wordpress plugin to access and manage a database brings security risks with it.

In most cases it might also break your wordpress website.

So the best way to access and manage your database is directly from the server.

Description for each tables

Optimize wordpress database - Database structure tree diagram
Optimize wordpress database – Database structure tree diagram
  • wp_commentmeta – This table stores meta information about comments
  • wp_comments – This table stores comments
  • wp_links – This table stores links from the blog and overall website
  • wp_options – This table stores settings from theme and wordpress CMS
  • wp_postmeta – This table stores meta information about posts
  • wp_posts – This table stores all the posts from wordpress website
  • wp_termmeta – This table stores meta information about tags and categories
  • wp_terms – This table stores all the tags and categories from your wordpress website
  • wp_term_relationships – This table stores all the data from wp_terms as its associated with it
  • wp_term_taxonomy – This table stores descriptions for category, links and tags
  • wp_usermeta – This table stores meta information about users
  • wp_users – This table stores all the information about users

The Overhead and Size you should remember

There are two things in the wordpress database that you should keep in mind is the “overhead” and the “size”.

Overhead column – Overhead table is like a temporary disk space that is used by the wordpress database to store the typical database queries.

But keep in mind the overhead size will increase gradually with the time.

Size column – The size column is nothing but an indicator to show the overall size of the database. The more the content inside the database the more the size of the database.

Question – Does having an overhead in the database affects the performance of the website?

Answer – Its absolutely normal to have an overhead inside the database and it really doesn’t affects the overall performance of the website unless it gets too high. Though the actual amount that should be considered too high is the grey area of Overhead column.

When you will optimize wordpress database it will eventually delete the overhead and reduce the size.

Every database needs some kind of maintenance and optimization on regular basis. Especially in a case of a website that get’s updated on a daily basis.

You’re doing this because you want to make the performance of your website even better.

It’s similar to doing a servicing of a car. When you drive your car for specific kilometers or miles, you take your car to workshop so that you can service it.

In that brief service period, the oil of your car is changed, wheel alignment is done, brake oil is changed and every nook and corner of your car is cleaned and tested.

This is so that you can keep your car in a great condition so that it can perform well on roads. The same thing can be applied for website and this is what optimization means.

Cleaning up – Optimize WordPress Database Guide

When it comes to cleaning up your wordpress database and optimizing it, there are four possible ways through which you can do the cleanup job.

  1. The first one is by using the “SQL Queries”
  2. The second method is by using the interface of “PhpMyAdmin”
  3. The third method is by using wordpress inbuilt tool
  4. The fourth one is by using the external plugins.

Lets check these methods simultaneously instead of checking it out one by one.

As i mentioned earlier, when you optimize wordpress database it cleans up the overhead and reduces the overall size of the database, so here you can use an SQL Query “Optimize table” to clear up the area or table affected by overhead.

In other words the query will be like this

SQL Query – OPTIMIZE TABLE ‘wp_posts’

Where “Optimize table” is the query and “wp_posts” is the table name. The other way you can do this is by using the in-built method of PhpMyAdmin database management tool.

Optimize wordpress database using PhpMyAdmin
Optimize wordpress database using PhpMyAdmin

Select “Check all” option in the PhpMyAdmin and then select the dropdown menu called as “With Selected” and then select the option “Optimize table”, after that the PhpMyAdmin will take sometime to optimize wordpress database and then you will get a green signal which will say – “Your database has been optimized successfully.”

Also another option that you should keep in mind is the “Repair table” option that is located just below “Optimize table” option. This option will help you to fix the table which is corrupted, you can also repair the table using the “Repair table” query.

SQL Query – REPAIR TABLE ‘wp_posts’

Where “Repair table” is the query and “wp_posts” is the table name.

Optimize WordPress Database using In-built tool

The WordPress content management system has another brilliant in-built tool that let’s you optimize and repair the WordPress database table. And to use this tool you need to add a one line code to your “wp-config.php” file.

You can say that this one line of code activates the tool so you can use it.

The code that you need to add – define( ‘WP_ALLOW_REPAIR’, true );

Once you have added the above mentioned one-line code to your “wp-config.php” file and access the tool by going to “www.yourwebsite.com/wp-admin/maint/repair.php.”

Note : Replace  “www.yourwebsite.com” with the domain or the URL of your website.

 

Optimize WordPress Database - In-built tool
Optimize WordPress Database – In-built tool

This in-built optimization tool will attempt to repair and optimize your wordpress database tables from time to time, although you will notice that the tool won’t be able to repair certain tables. Hence, if you did not succeed at the first attempt, just don’t worry and run the tool again.

The tool offers you two options you can use

  1. Repair database
  2. Repair and optimize database

The first option will fix the corrupted tables and the second option will fix the corrupted tables as well as optimize every table that has not been optimized.

However, there is one drawback to this tool and that is, you don’t need to log-in to your admin account to use this tool, you just need to access the appropriate URL and then you can use this tool.

But this allows anyone to use this tool whether you want them to do so or not.

In case of data security, this tool, doesn’t pose any potential risk, however, if someone accessed this tool and used it while you are using your website, then chances are, that your site my break down.

So to avoid this risk, all you need to remove the below mentioned code from your  “wp-config.php” file.

The code that you need to delete – define( ‘WP_ALLOW_REPAIR’, true );

Remove this code after you finished working with this tool, by removing this code the tool will get deactivated.

Removing the bloatware from your WordPress website

Optimization and Repair is not the only way maintain the database. You need to clear the trash data too that’s generated every single time you update your website.

This trash data includes stuff like, posts revisions, transient data, deleted plugin data, unnecessary backups, spam comments etc. All this unnecessary data makes the website slow and less efficient.

There are countless ways through which you can clear all the unnecessary mess to make your website perform better. But since we are discussing about removing the bloatware, you might wonder that, why doesn’t this data gets cleared during optimization and repair?

Well, we have already mentioned in this article earlier, that, optimization and repair, fix the corrupted tables and reduces or cleans the overhead which decreases the size of the database.

Hence, as a result, it doesn’t clear the bloatware that is stored in the other tables.

Now moving towards, removing the rest of the bloatware, we will focus on “Revisions” feature of wordpress.

What are posts revisions or revisions?

Revisions system was first introduced in WordPress 2.6 and to be honest, despite this feature generate huge amount of bloatware, we can’t deny the fact that this feature is extremely useful.

What this feature does is, if you’re working on a new blog article, it will automatically generate draft copies and update of your blog articles, so that later, if you need you can revert back to the older version or the blog articles.

Posts revisions - Optimize WordPress database
Posts revisions – Optimize WordPress database

These are the posts revisions which were generated while we were working this article. As you can see in the screenshot above, you can compare the older versions of article and it even allows to restore the older version.

However, as useful as this feature may seem it has a huge drawback. Unfortunately, wordpress has not put any limitations on the revisions system. That means, it can generate unlimited number of draft copies.

Because of this the revision system becomes inconvenient, and what’s more those draft copies doesn’t get cleared automatically and thus it becomes a bloatware that ultimately affects the performance of your website.

So does that mean you can’t solve this issue? and wordpress doesn’t have anything to solve this?

Well its not like that, even though wordpress has not put any limitations it will allow you to limit the number of revisions with a single line of code.

Just add the following code in “wp.config.php” file

Code to limit post revisions – define( ‘WP_POST_REVISIONS’, 2 );

The number “2” in the above code defines the number of posts revisions that will be generated by revisions system. You can also disable the posts revisions completely with this code :-

Code to disable Pots Revisions – define( ‘WP_POST_REVISIONS’, false );

However, its better not to disable the posts revisions completely, even though it helps to reduce the size of database. That’s because this feature of posts revision also acts as a fail-safe system.

What is a fail-safe system that we mentioned earlier?

Let’s assume that you’re working on a long article and then suddenly, you lose your internet connection or you close the web browser accidentally. If you have the “Revision system” disabled then you could lose everything you have been working on. That’s something you really don’t want right?

That’s why you need “Revision system” as it helps you to bring the data back and prevent any unfortunate loses.

As a result limiting the numbers can solve the issue. You can also delete the revisions once the article that you have been working on is published.

But there are two ways to do so, You can delete it from wordpress or you can use PhpMyAdmin to clear the revisions.

However, we would advise you to use the correct command or you may end up breaking your website.

WP Optimize - Optimize wordpress database
WP Optimize – Optimize wordpress database

To avoid any issues with the database we always use this plugin called “WP-Optimize” that help us clear all the bloat generated by working on the website.

Cleaning up the autosaves.

Auto-save is another great fail-safe feature of wordpress content management system. As the name suggest what this feature saves the draft copies of blog posts too.

So in case of uncertainty you can retrieve back what you have been working on. But once again after publishing the posts, these autosaves doesn’t get deleted as a result, it becomes bloatware and affects the performance.

Hence it becomes our job to clear the mess of the autosaves once we are done working on the blog articles.

However instead of limiting or disabling the autosaves we will increase the time interval during which the wordpress automatically generate autosaves from the blog posts.

Keep in mind that the autosaves are generated every 60 seconds. You can increase the interval with this one line of code that you can add in the “wp-config.php” file.

Code to increase the time interval – define( ‘AUTOSAVE_INTERVAL’, 120 );

Where the “number 120” is the seconds. We advise not to disable the autosave and for that the reason is same as explained above in the “Revison system” section.

Also you can use the WP-Optimize Plugin to clear the autosaves which bloats your website.

Working on the SPAM Comments

If you own a blog which is extremely popular, then its good to guess that you can even get spam comments.

These spam comments also comes into the category of bloatware.

Now to get rid of those spam you can use “AKISMET Plugin” the most popular plugin to remove the spam comments.

Akismet Plugin - Optimize WordPress Database
Akismet Plugin – Optimize WordPress Database

Whether you choose a free basic version of Akismet plugin or a paid version of Akismet Plugin, It provides you two options :-

  1. Silently discard the worst and the most persuasive spam comment
  2. Always put spam comments in the spam folder for review

The first option detects obvious spam and deletes it although sometimes, legitimate comments which are not spam may be deleted.

As Akismet plugin is not that perfect in differentiating between spam comments and the legitimate comments. So always opt for the later option which puts the spam comments in the spam folder so you can review them one by one. Although keep in mind that the spam folder will be cleared within the span of 15 days.

Also, you can use the  WP-Optimize Plugin to clear the spam comments too.  And you can also empty spam directly from the wordpress comments itself.

Using a MySQL Query you can also delete the spam comments from your wordpress website :-

Query to delete the spam comment – DELETE FROM wp_comments WHERE comment_approved = ‘spam’

Deleted items and WordPress transient

Whenever you delete anything in wordpress, let it be, the blog posts, page or any image it is transferred to the trash folder. This is another kind of fail-safe system provided by wordpress.

Why it is a fail-safe system? that’s because let’s assume if you mistakenly delete any kind of data, on which you have been working on for along time. If it wasn’t for trash folder, then you can say that the data on which you were working on, would be deleted permanently.

If you are keeping the trash folder clean and empty then you don’t need to worry about the trash data making your website slow.

But if you have deleted huge amount of data then you can click on “Empty trash” to clean the data, or you can use WP-Optimize Plugin.

However, the trash folder has functionality to clear the entire data which is stored inside in 30 days.

If you can want you can limit the number of days by adding this single line of code in the “wp-config.php” file.

SQL Query to limit the number of days – define( ‘EMPTY_TRASH_DAYS’, 4);

The above query limits the days from 30 to 4 days, in which the number “4” is used for defining the number of days in which the trash data should automatically be deleted.

You can also disable the trash system itself by replacing the number “4” to number “0” however, we would advise you not to do that as it would also disable the fail-safe system.

Similarly, you can delete the expired wordpress transients which affects the performance of your website using WP-Optimize Plugin.

Question – What are transient or WordPress transient?

Answer – Wordpress transient offers a way of storing data temporarily in the wordpress database. These transient records are stored in the WordPress options tables and the ones that get expired, can affect the performance of your website once it became bloatware. Hence the expired transient should be cleared when there is a need to do so.

Unused Plugins and Themes

Whenever you use any new theme or plugin the data is stored inside the database. However, when you uninstall the plugin or theme, the data is not deleted. This is because, if you ever decided to use that theme or plugin once again, you can just install the plugin and then you don’t need to re-configure it, as the data from your previous use is still remaining.

However, if you decided to stop using that theme or plugin once and for all then you can remove the data that is stored inside the database too.

Advance Database Cleaner - Optimize wordpress database
Advance Database Cleaner – Optimize wordpress database

Some plugins and themes provide options to delete that stored data once you uninstall them, but majority of plugins and themes don’t provide that option.

Unfortunately there is no manual option or SQL query through which you can clear the data for unused themes and plugins that is stored inside the wordpress database. Hence for this we use “Advance Database Cleaner Plugin“.

This plugin allows you to clear every unused data related to old themes and plugins, along with that it also provides auto-optimize feature, so that it can optimize your wordpress database time-to-time on schedule periods.

Wrapping up and Final Thoughts

Though our company “Krytech Web Security Solutions” have provided varieties of development and security services for our clients, but we have also helped people to solve problems with their WordPress website and have even developed wordpress themes and plugins for them.

Over the course, we have noticed that, there are very less people who take active measures to keep their website performance at its peak. We hope this guide to optimize wordpress database will help many small and individual website owners for maintaining their website.

In future we hope to write more such articles on WordPress so we hope that you will subscribe to us and stay updated.

As of now, if you have some queries or if you have something to say then, you can comment down below in the comment section. We will try to respond you fast and at the best of our abilities.



Join our Mailing List to Get Exlusive Updates

Enter Your Email address to Subscribe



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.