WordPress Managed Hosting - 40% Off For 4 Months - Coupon Code: BFCM2021 Avail Now

How To Optimize WordPress Database Through Code And Plugin

Moeez — March 14, 2017 4 Minutes Read

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

WordPress Database Structure

Optimize the WordPress Database

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.

OPTIMIZE TABLE

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:

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

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:

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:

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.

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 fine tune the process with the help of .htaccess files.

 

Although this code clears the client browser caching completely. You can add a code snippet code to your application so that the recent changes reflect into the client’s browser. In the <head> 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.

 

 

Create Faster WordPress Websites!

Free eBook on WordPress Performance right in your inbox.


    Create Faster WordPress Websites!

    Free eBook on WordPress Performance right in your inbox.

      Moeez is ‘The’ blogger in charge of WPblog. He loves to interact and learn about WordPress with people in the WordPress community. Outside his work life, Moeez spends time hanging out with his friends, playing Xbox and watching football on the weekends. You can get in touch with him at moeez[at]wpblog.com.

      THERE'S MORE TO READ

      Newsletter

        WordPress Help Zone - Ultimate WordPress Pit-Stop

        Learning WordPress? Or are you expert enough to help others? Join our WP Facebook group!