WordPress database optimization – A complete tutorial 2017


When it comes to website performance, database optimization is a huge challenge. The database stores almost all of the website content. For example your posts are there. This also applies for pages, comments, website settings, forms, links and so on. From that point on, it is easy to conclude that regular updates make the database larger which may affect the overall performance. This raises two other questions “What is WordPress database optimization and how to guarantee the best performance ?“.

Well, the answer is easy. The best way to maintain a well organised database is to remove the unnecessary elements. Here however, comes another question “How to identify the important elements and remove the rest ?”. To address this question, we will first need to understand how the database works.

WordPress database – what does it store ?

Understanding the inner working of the database is important. This especially applies for the cases in which the database affects negatively the performance. By default WordPress has 11 tables, the number of which may change during the development. Usually the reason for large number of tables are additionally installed plugins. Some plugins use code that generates new tables in the main structure to save some information there. In this tutorial, we will be looking at the default structure. The topic today is best optimization practices we could apply to wordpress database. Here is quick review of the default tables:

  • wp_commentmeta – here you can find information about the comments
  • wp_comments – this table stored the comments
  • wp_links – it contains links
  • wp_options – here you can find the general settings
  • wp_postmeta – as the name suggest, here you can find post meta information
  • wp_post – this tables contains posts, pages and CPT
  • wp_terms –  you can find tags and categories here
  • wp_terms_relationships – in simple terms, this table contains information about the relationships in your data
  • wp_term_taxonomy – here you can find more information about taxonomy used in wp_terms section.
  • wp_usermeta – this table holds user information
  • wp_users – here are the users

Basic WordPress database optimization

The basic approach here is based on simple interaction with PhpMyAdmin. This is most commonly used database management utility. It supports optimize and repair options. I recommend this approach to users with little to no experience. PhpMyAdmin is a well known tool that can be found in almost all web hosting control panels. There are a few simple steps you have to take:

  • Click PhpMyAdmin
  • Select your database from the left menu
  • Mark the tables you want to optimize or repair
  • Select the preferred option at the bottom and then click Go

If the database in question is quite large, let’s say over 500 MB, then I recommend backup creation. Based on my personal experience, I can say that this would work without any troubles with databases up to 100 MB. The following screen shot illustrates the whole interaction :

WordPress database optimization - A complete tutorial 2017

WordPress database optimization – A complete tutorial 2017

Another similar approach that would achieve the same goal is to enable the so called native optimization tool. To do so, you have to add the following line to wp-config.php file:

define('WP_ALLOW_REPAIR' , true);

When you are ready with this line and save the file you were working on, you can directly access the tool using this url:

http://yourdomain.com/wp-admin/maint/repair.php

This way WordPress will be forced to check and try to repair each one of the tables in your database. Note that depending on the server settings the execution may fail, so you will have to run it again.

Advanced WordPress database optimization techniques

The next chapter in this tutorial covers more advanced techniques that can either be done directly in the database or through wp-admin section. Let’s start with revisions.

Managing WordPress revisions

WordPress revision system was first introduced in version 2.6. Its primary goal is to keep copy of every draft and post of your blog or website. Unfortunately by default it does not have maximum number of copies. As a result of this the database size gets quite large. There are two simple options to overcome this potential issue. We can either manually limit the revisions or completely stop  them. Here is how to do it:

To limit WordPress revisions to 2, just add the following line to wp-config.php file and then save it:

define('WP_REVISIONS' , 2);

Another possible course of action is to completely disable this feature by adding this line to wp-config.php file:

define('WP_POST_REVISIONS', false);

Generally speaking disabling this fail save system is not recommended. If you accidentally close your browser and lose internet connection, then you will not be able to retrieve your data. With that being said I would recommend keeping at least 2 copies. Another important point to note. Stoping revisions will take effect from now on. In other words the old copies will not be removed. To remove them, you can either use plugin or delete the old entries using PhpMyAdmin.

Managing spam comments

It is true that nowadays a lot of blogs and websites suffer from spam. Applying a permanent solution is really hard task. Fortunately there are a few successful approaches. The first one is installing and activating Akismet. Another possible course of action is to disable completely the comments feature or manually review the new ones. Later upon decision, you may either approve or mark them as spam. Depending on the website specifics and size, different approach might be applicable.

Deleted items in WordPress

WordPress has another fail save feature that helps to avoid data loss. Whenever you delete an item from the website, it goes first to trash and sits there until its complete deletion. This again has pros and cons, but disabling this feature is not recommended. You can automate the trash cleaning instead. To do so, you have to add the following line to wp-config.php file:

define( 'EMPTY_TRASH_DAYS', 2 );

Managing unused themes and plugins

As you may know some well known extensions create their own tables in the database and this way make it largest. In some cases, when you remove a particular extension its tables may remain in the database, so you will have to review it and remove the entries manually. This can easily do it using PhpMyAdmin. Of course this requires more advanced skills and knowledge. If you are new to the field, then generating a working backup would avoid a huge headache.

WordPress transients

Dealing with transient records is another important part of WordPress database optimization. In essence, transient records allow developers to store temporary records for some reason. It could be fail save purpose, some additional checks or verifications. With that being said, careful inspection would give a positive effect over the optimization. There are a number of useful plugins that would help achieve good cleaning. The choice depends on your personal needs. Another approach is achieving the same using Mysql query. The following one is a good example:

$wpdb->query( "DELETE FROM `$wpdb->options` WHERE `option_name` LIKE ('_transient_%')" );
Summary
WordPress database optimization tutorial - the best performance practices
Article Name
WordPress database optimization tutorial - the best performance practices
Description
What is wordpress database and what information is stored there? Basic and advanced optimization techniques. Managing wordpress revisions and spam comments. Database clean up tutorials
Author
Publisher Name
wppotion


Do you want to share your opinion?

Your email address will not be published. Required fields are marked *


*

We are not industry specific. We are WordPress specific. We work with everyone to help them find solutions with their troubles.
If you show us the problem you are experiencing, we will show you how to fix it. It is that simple.


Reported WordPress issues

  1. Choosing wordpress theme and technology to build ecommerce website
  2. Swiper slider ignore custom settings
  3. How can i start the async task in wordpress for once?
  4. Please suggest a good solution plugin
  5. GoDaddy Managed WordPress site preloading non-existent CSS and JS
  6. How to display excerpt for custom post type
  7. Query all posts of a custom taxonomy term
  8. Website creation, should I use existing services? [on hold]
  9. Wordpress opens site homepage only on localhost [on hold]
  10. I want to make a travel agency website with wordpress, where people can book flights with multi city destinations [on hold]

WordPress problems we are working on

  1. General question on best method for setting and getting server configurations
  2. Are the downloadable themes from wordpress.org free?
  3. AMP - Accelerated Mobile Page. Please, how do I set a custom template to a Specific Page in WordPress AMP? [on hold]
  4. Visual Composer Media Grid not loading
  5. How do I use noindex, nofollow at specific wordpress pages
  6. Wordpress upload image with php and set featured with javascript
  7. How to add ACF image field to WP admin table column of custom taxonomy
  8. What will be the best JS library/framework for the frontend part with WordPress backend?
  9. I need some correct CSS for my filtered portfolio [on hold]
  10. How to fix html output on this wpml function?

Resolved issues

  1. Code to remove “Search Jobs” button doesn't work [on hold]
  2. Remove social menu footer
  3. JQuery Animate function not working commas/decimals
  4. W3 Total Cache - Stop caching a fragment
  5. Placing php ,html, php in one line [duplicate]
  6. wordpress programmatically add post tags
  7. MySQL Duplicate Entry for non Unique column update in WordPress
  8. How to display by default 2 value of multi select field in gravity forms [on hold]
  9. How can i prevent a flexible content field to only render the last item?
  10. Media Query for JS
wppotion - powered by persistence and passion