The WordPress Database stores all content of the website, including blog posts, pages, comments, and custom post types such as links, form entries, and portfolio items. To complicate matters, the database also stores all settings and configurations for the website, themes and plugins.

If you update the website regularly, the database will continue to grow in order to accommodate all the changes. A large database can significantly affect the performance of the website because the server will need a lot of time to retrieve information from the large database tables. This is why database optimization is so very important for any WordPress powered website.

WordPress Database Structure

WordPress database has the following interrelated table

• wp_commentmeta – Stores meta information about comments.

• wp_comments – Stores the comments made on the website.

• wp_links – Stores blogroll links.

• wp_options – Stores the options defined in the admin settings area.

• wp_postmeta – Stores post meta information.

• wp_posts – Stores data about and for posts, pages, and other custom post types.

• wp_terms – Stores post tags and categories for posts and links.

• wp_term_relationships – Stores the association between posts, categories and tags and the association between links and link categories

• wp_term_taxonomy – Stores the description about the taxonomy (category, link, or tag) used in the wp_terms table

• wp_usermeta – Stores meta information about users

• wp_users – Stores users information

For more information about the structure and core tables of the database, check out the database description page on WordPress.org

Optimize the WordPress Database

You can optimize tables that are affected by the overhead (too much clutter) by using the SQL command OPTIMIZE TABLE. For example, you could optimize the wp_posts table by executing the following SQL query:

OPTIMIZE TABLE ‘wp_posts’

All you need to do to optimize your database is to click the Check All box, and then click the Optimize button.

Once the process of optimization finishes, you will see the following screen:

Handle Spam Comments

Spam comments can also be deleted through the following SQL command:

DELETE FROM wp_comments WHERE comment_approved = 'spam'

Similarly, all comments awaiting approval can be deleted by using the following SQL command:

DELETE FROM wp_comments WHERE comment_approved = '0'

Deleted Items

Whenever you delete an item (blog post, page, image, comment, or link) from the WordPress website, it is sent to the trash folder.

First, put this in the wp-config.php:

define( ‘SAVEQUERIES’, true );

Then, in the footer of the theme, add this block of code:

";
   print_r( $wpdb->queries );
   echo "

Deleted items will continued to be stored in the database until the trash is emptied. By default, trash items are permanently deleted after 30 days.

The number of days before the trash is emptied can be changed by adding the following code to the wp-config.php file:

define( 'EMPTY_TRASH_DAYS', 7 ); // 7 days

This will empty the trash after seven days. The trash system can be completely disabled by adding the following line of code to the wp-config.php file.

define( 'EMPTY_TRASH_DAYS', 0 ); // Zero days

Optimize Database Using WP-Optimize

WP-Optimize is an effective tool for automatically cleaning out the WordPress database so that it runs at maximum efficiency.

WP-Optimize can be used to remove post revisions, drafts, spam comments, unapproved comments, comments in the trash, transient options, pingbacks, and trackbacks.

WordPress Caching

While browsers do cache some data automatically (for example JavaScript and CSS files), you can finetune the process with the help of .htaccess files.

# BEGIN Gzip



AddOutputFilterByType DEFLATE text/text text/html text/plain text/xml text/css application/x-javascript application/javascript



# END Gzip

# BEGIN EXPIRES



   ExpiresActive On

   ExpiresDefault "access plus 10 days"

   ExpiresByType text/css "access plus 1 month"

   ExpiresByType text/plain "access plus 1 month"

   ExpiresByType image/gif "access plus 1 month"

   ExpiresByType image/png "access plus 1 month"

   ExpiresByType image/jpeg "access plus 1 month"

   ExpiresByType application/x-javascript "access plus 1 month"

   ExpiresByType application/javascript "access plus 1 month"

   ExpiresByType application/x-icon "access plus 1 year"



# END EXPIRES

Although this code clears the client browser cache completely. You can add a code snippet code to your application so that the recent changes reflect into the client’s browser. In the tag:

Conclusion

Cleaning database is very important practice, especially when the size of the database is large enough. In this article, I have discussed several ways of optimizing WordPress database through SQL queries, .htacces rule and code snippets. If you further help in optimizing your WordPress database, do leave a comment below and I will return to you ASAP.

The post How To Optimize WordPress Database Through Code And Plugin appeared first on WordPress Tutorials, Tips and Reviews.