A Noteworthy Guide to Optimize WordPress Database Through Code and Plugin

To operate efficiently it becomes mandatory for a WordPress website to have a robust database. But, it gradually the database gets cluttered with time. It includes a lot of unnecessary data like spam, revisions, and drafts.

It also contains extra files created by themes and plugins. However, all the files are not removed even after un-installation. This ensures that you have the data preserved in case you re-install them later.

If the database is not maintained it affects website loading speed. Therefore, regularly cleaning it like once in a month is an advisable practice. This short read will help you with tips to optimize WordPress database.

But first, let us understand the structure. It is created automatically upon installation. If you know it well then it will help in managing them effectively.

wp_links -This contributes to storage of blogroll links
wp_posts -All the posts data including custom post types and pages is stored
wp_usermeta -Metadata related to the user is stored
wp_postmeta -Meta information related to posts is stored
wp_commentmeta -Meta information related to comments is stored
wp_options -The admin setting area option related data is stored
wp_comments -Storage related to all the comments on the website
wp_user -Dedicated storage for users
wp_terms -Post tags and categories are stored
wp_term_relationships -Relationship between categories, tags, and posts is stored
wp_term_taxonomy -Storage for all the data related to taxonomy

If you visit the official website of WordPress you can find comprehensive information on its working.

Optimize WordPress Database with Code

SQL Command and Optimize Table

Tables are well-known for creating overheads on the top. Hence, this method can be used to clean it. It runs on wp_posts and the command is as follows

OPTIMIZE TABLE ‘wp_posts’’

If in case you are using PHPMyAdmin then also optimization is possible. All you have to do is right-click the required ones after selecting. Then select the ‘Optimize Table’ option and it will be done.

Spam Comments

One of the reasons for database clogging is spam comments. But it only takes a simple SQL command to remove it.
DELETE FROM wp_comments WHERE comment_approved = ‘spam’

If required there is another command that helps in deleting the pending comments from the website.
DELETE FROM wp_comments WHERE comment_approved = ‘0’

Handling Trash Items

If you do not deliberately delete anything from WordPress it stays forever. This applies to everything including posts, comments, images, and even links. All that happens is their redirection to the trash folder.

According to the default setting the trash is cleared every thirty days. Hence, all the files will be compiled in one location for one month and occupy space. This can be harmful sometimes and hence to modify it we can change the default timer.

Add the following code line in the wp-config.php file

define( ‘SAVEQUERIES’, true );

In the footer section of the theme add the following block code. Ensure that the file name is footer.php

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

In order to change the no. of days for storage of trash add the below code to the wp-config.php file
define( ‘EMPTY_TRASH_DAYS’, 3 ); // Empty trash every 3 days

If you change the number of days to zero then it disables the trash system. Although, it is highly recommended that you avoid it until it is necessary.

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

Delete Revisions

There is no doubt that revisions are essential but it is one of the contributors to cluttering. Hence we can set the revision limit accordingly by including the following code in the wp-config.php file.
define( ‘WP_POST_REVISIONS’, 4 ); // Set the number of revision per post to 4.

Autosave

Modifying the interval of Autosave can help to optimize WordPress database. The usual time for the autosave feature is sixty seconds which is a reason the cluttering occurs. You can add the following code to modify the interval of autosave.

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

If your work involves working with sensitive information then it is not recommended to make any changes in this feature.

Optimize WordPress database with Plugin

Coding is not a favorable solution for everyone working in optimization. You must avoid it if you are not confident about the same. Instead, use the other preferable method which is the plugin route.

It is highly recommended to use the WP-Optimize plugin for this process. It is a popular and widely used program that has earned the reputation of being perfect for this task. The installation, activation, and the cleaning is easy with this program.

In case you are looking for an alternative, then use either Plugins Garbage collector or WP-DBManager.

When it comes to business dealings with technical aspects of WordPress, then plugins should be inevitably used. For instance, any website that is reviewing the other website builders can use plugins and improvise their business.

Conclusion
This leads to the end of this guide. We have explained to you the paths i.e, coding, and plugins that can effectively do the job for you. Analyze your compatibility and then select one from them.