Sign up to Fruitful code

Already a member? Sign In

Sign in to Fruitful code

New to Fruitful code? Sign Up

Please set me up with a new password

To reset your password, enter your email address and we'll send you an email with instructions.

New to Fruitful code? Sign Up

Can not reset password

The link or restore key is not valid

A Noteworthy Guide to Optimize WordPress Database through Code and Plugin

WordPress is a great content management solution. But, it is not perfect. The database, for instance, needs to be optimized from now on then. As a core package, WordPress doesn’t maximize the database automatically. That leads to only two options – either using code or plugin.

In today’s article, we will focus on the process of optimizing your WordPress database. To ensure that we give you better control of how to do it, we will cover both the code and plugin route.

Let’s get started

Any WordPress website requires a robust database to operate efficiently. With time, it is common to see database getting cluttered. There are post revisions, drafts, spams and a lot of data or information that is unnecessary. On top of that, plugins and themes also create extra files when one installs them. However, when you uninstall them, the data is kept so that your settings are saved if you decide to install them back later on.

If you don’t keep your database clean, you will slowly start losing website speed which will impact both SEO and visitors experience. That’s why it is always a good idea to regularly clean your database. We recommend cleaning up your database once every month. If you are building a blog, the tips will surely help to maintain a healthy blog over time.

Before we start, let’s understand the WordPress Database structure. WordPress automatically creates them upon installation. Learning about them will help you manage them better:

  • wp_links – Storage of blogroll links.
  • wp_usermeta – Storage of user’s related metadata.
  • wp_posts – Storage of all the post data, including pages and custom post types.
  • wp_postmeta – Storage of post meta information.
  • wp_options – Storage of admin setting area’s options.
  • wp_commentmeta – Storage of meta information about comments.
  • wp_comments – Storage of all the comments made on your site.
  • wp_term_relationships – Storage of all the relationship between categories, posts, and tags.
  • wp_users – Storage of users.
  • wp_term_taxonomy – Storage of taxonomy related data.
  • wp_terms – Storage of posts categories and tags.

If you want to know more about how WordPress database works, check out the official database description before proceeding further.

Optimizing Database through Code

Using the SQL command OPTIMIZE TABLE:

The tables frequently create an overhead on top of them. To clean it, you can run the SQL command OPTIMIZE TABLE. It will run on “wp_posts.” Check the command below for a better understanding:

OPTIMIZE TABLE ‘wp_posts’’

You can also optimize tables if you are using PHPMyAdmin. For that purpose, you need to select the required ones, and then right-click. After that select the “Optimize table” option and you are set. Check the image below to get a clear view:

php-my-admin-screenshot-1

 

Removing spam comments

Spam comments can clog the database considerably. The good news is that you can remove spam comments by running one simple SQL command as below:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’

You can delete the pending comments on your website by using the SQL command below:

DELETE FROM wp_comments WHERE comment_approved = ‘0’

Improve Handling of Trash Items in WordPress

Nothing is permanently deleted from WordPress until you do that deliberately. That happens as the result of removing any item including image, posts, comments, or even links. They are sent to the “trash” folder as well.

By default, trash is removed from the system every 30 days. That means that it will keep growing and then the trash folder will be emptied at the end of the 30-day period. That can be too much for some sites. To keep you out of that, let’s change the default timer.

To do so, first, you need to add the following line of code in the wp-config.php file:

define( ‘SAVEQUERIES’, true );

Now add the following block of code in the footer section of your theme. The file name should be footer.php.

<?php
if ( current_user_can( ‘administrator’ ) ) {
global $wpdb;
echo “<pre>”;
print_r( $wpdb->queries );
echo “</pre>”;
}
?>

To change the number of days for trash to be stored until it is deleted, add the line of code mentioned below to the wp-config.php file:

define( ‘EMPTY_TRASH_DAYS’, 7 ); // Empty trash every 7 days

You can also disable the trash system by changing the number of days to 0. However, you should better not do that. One should have a strong reason for that.

define( ‘EMPTY_TRASH_DAYS’, 0 ); // Trash disabled

Remove Revisions

Revisions are essential, but they clutter the database. You can set the revision limit accordingly. To do so, add the following line of code in the w-config.php file:

define( ‘WP_POST_REVISIONS’, 3 ); // Set the number of revision per post to 3.

Autosaves

Last, but not the least, you can change the interval at which autosaves take place. Normally, it autosaves every 60 seconds. Thus the database gets cluttered. To change the autosave interval, add the following line of code:

define( ‘AUTOSAVE_INTERVAL’, 200 ); // Autosave will happen every 200 seconds.

Note: Do not change this if you are working with sensitive information requiring routine saves.

Optimizing Database Through Plugin

Optimizing the database through code is not for everyone. If you don’t feel confident playing with the code, there is always the plugin route. The only plugin that we recommend using is WP-Optimize. It is a database optimization plugin that does its job. Everything is easy with this plugin. You can easily install it. Once activated it will be set to do regular cleanups. You can use the plugin to clean database stats, post revisions, and so on. If you are looking for an alternative, opt between WP-DBManager or Plugins Garbage Collector. It is a good idea to use plugins for businesses dealing with the technical aspects of WordPress. For example, a website reviewing website builders or bikes can use the optimization plugin and focus on improving their business.

Conclusion

That leads us to the end of our WordPress database optimization guide. So, which path you are going? Code or plugin? Comment below and let us know. We are looking forward to chatting with you!

 

The post was contributed by Madan Pariyar, a blogger at WebPrecious and a digital marketing strategist helping clients to resolve their website woes. When not busy with all things, you may find him occasionally watching movies, traveling and spending time with his family.

Ilya Pavlov

Ilya Pavlov

Creative director, web designer, developer and founder of Fruitful Code.

Top

We use cookies to give you the best online experience. By agreeing you accept the use of cookies in accordance with our cookie policy.