Every WordPress website needs to be optimized time to time, in this article we are going through how to Optimize WordPress Database. WordPress is the sole resting place for all your content. It stores your comments, links, portfolio, blogs, and your personal website settings. The biggest issue that large websites have to face is their ever growing database. A larger and poorly optimized WordPress database can cause serious speed issues on your website. The site takes longer to navigate that could compromise the overall customer experience.
Optimization is critical for maintaining and improving the efficiency of your web page while your database continuously grows. It can seem like a daunting task but the guidelines discussed in this tutorial should make it far easier and manageable.
In this article we will cover the following subjects:
Before you can go towards optimization it is important to understand the basic framework of the database on WordPress. Currently, WordPress has 11 basic tables that it uses to save different pieces of your settings and content. But in reality, WordPress websites tend to have multiple tables that dedicated to storing the plugin settings, information, and other important data in WordPress. Themes can also increase the size of your database. Essentially every plugin and Theme that you save on your site is going to increase the size of your database that’s why it’s very important to optimize WordPress database.
The diagram below gives a brief outlook of the WordPress Database.
How to Optimize WordPress Database
There are different approaches that one could take when it comes to database management but generally, phpMyAdmin is considered to be the best tool to optimize WordPress database as well as manage it. Other tools such as Adminer can also be effective in managing and optimizing WordPress database if you are not comfortable with PHPMyAdmin.
Managing your database with other tools can be effective but have some security shortcomings due to which we highly recommend going with PHPMyAdmin.
Your database would be represented in form of two columns, one represents size while the other represents overhead. The size of the table is generally determined by the amount of data stored on it. Overhead, on the other hand, is the cache or temporary space that is dedicated to the database to access or store different queries. Usually, with time and increased usage, the overhead of the table grows larger.
It is a normal practice of WordPress managed pages to contain overheads. These overheads in the usual course of business would not affect the overall performance of your page but can become a hampering factor if they get too high. The optimization tool would regulate the overheads and would also decrease the size of the database. Both of these factors would have a net effect of optimizing WordPress database. The Table management is akin to disc defragmentation in HDD. This process streamlines the files and reduces the response time.
Why should you reduce overheads?
Databases tend to grow slowly over time as they acquire more data, get longer indexed paths and so on. The optimization exercises lighten the load on the engine resulting in improved performance and better response time. While optimizing tables only select those tables that the highlighted in grey so you get the best results.
A simple “WP_Posts” command should do the trick. The phpMyadmin does not require any length SQL commands, the same can be accomplished from the main drop-down menu. Simply select all the tables and click on Optimize table and select go, the rest would be handled by the Myadmin.
Repair Broken Tables
As an engine can break down over time similarly a website’s code can also get damaged over time. Variables, updates, plug-ins or thousand other issues can cause a table to unravel and become broken. WordPress has a useful tool for dealing with broken tables. Repairing broken tables rid your website of corrupted links, broken codes, and a horde of other nasty issues. It also improves the overall security of your page.
Simply add the following line to your WP-Config.php file
Define( ‘WP_ALLOW_REPAIR’ , true );
Adding this line would unlock the repair feature for your page. Access the maintenance tab of your site.:
The script would take care of the rest. It would repair tables one by one and even optimize them during the process. In some instances the repair tool may fail to run properly in such cases simply running the tool again should fix the problem.
The link would provide you with 2 options use them based on your need:
Repair Database = Simply repair the broken tables would be quicker
Repair and Optimize = This option would repair and optimize the tables usually takes a bit of time
Note: Always remove the repair code from your WP-Config.php file once you have completed the repair exercise. Failure to remove the code can lead to problems if someone else manages to get the link to your WP site’s maintenance page and other people may get access to optimize WordPress database without your knowledge.
For some reason the tech companies are insistent on installing useless tools, apps, and codes that do nothing else but reduce the speed and efficiency of the website, WordPress is also guilty of this crime. The WordPress databases have a lot of bloats that takes a lot of space and hamper the speed and efficiency of the website. The following steps should help you significantly reduce bloat on your WordPress page:
It is a handy tool that saves the original draft of an article or piece of writing that you can revert to later if something goes wrong. But WordPress does not place any limit to the number of revisions that can be stored on your database, therefore it is highly recommended to a set a limit to the revision function so that the unnecessary load and bloat can be controlled.
Define( ‘WP_Post_Revisions’, 2 );
Simply replace the “2” in above code by any number of revisions you wish to set. Setting it to “False” would entirely eliminate the revision function from your site.
We do not recommend turning the revision function off completely as it provides a fail switch against the accidental closure of the browser or any other hardware failure.
It is important to note that the change of revision limit would not delete the previous revisions saved by the WordPress. You would need to optimize the database to get rid of those. “Optimize WordPress Database after revision” found here is a handy tool for that can be used to optimize WordPress database once or twice a day automatically.
Comments can take a lot of space and if your website receives a lot of Spam comments then they can cause serious efficiency issues in your database. WordPress automatically deletes Spam comments within 30 days but during that time the comments can take hundreds or even thousands of rows in your database. Therefore it is absolutely essential to managing spam comments.
Akismet & WPComment Cleaner are nice little tools that help you remove Spam comments instantly. WordPress has also introduced “Empty Spam” in its comment management page that does the same thing but you have to manually engage it.
It is not a foolproof system if left unattended it can also remove legitimate comments.
Spammers usually focus on the older content, therefore, disabling comments on content that is certain days older can significantly reduce the spam comments on your site. The setting for disabling comments is located on the Discussion setting page in admin view.
All the items that you delete from your site are moved to the trash folder. It is another security feature introduced by WordPress to reduce the risk of accidentally removing anything that you didn’t want to be removed. The trash folder only becomes relevant in optimization scenario when you are deleting a lot of stuff on daily basis otherwise it does not make any significant difference.
Deleted items are usually deleted automatically after 30 days have elapsed. You can change this setting by the following code.
Define( ‘Empty_Trash_Days’ , 5 ); // 5 days
The above code would set the automatic emptying of trash folder after 5 days. We recommend not reducing the automatic cleaning feature below 15 days as it can create the risk of permanently deleting items without chances of recovery.
Plugin and Themes:
The bulk of your plugin settings are stored in the WordPress Database. But these settings are not automatically removed once you decide to uninstall a plugin or switch to a new one. Over time these settings can become a substantial chunk and consume a lot of space on your database. The same is true for themes as well.
phpMyAdmin is a good option to manually get rid of these settings.
WPDBSpringClean is another good tool that can automatically remove any unused tables from your database.
Final Words About the article “Optimize WordPress Database”.
Maintaining a website is a proactive task that requires your participation and attention. Neglecting your WordPress’s maintenance can significantly reduce the penetration of your page. Taking the above-mentioned steps should reduce bloat and unnecessary baggage out of your database. This would result in making your website lighter and more efficient. If you are concerned about using phpmyadmin than a simple tool from WordPress called WP Clean Up can also be used.
This is a handy tool that can be used to remove spams, bloat, revisions, and multiple other tables in your database. It does not matter what tool you decide to use but it is certainly important to maintain the consistency and overall health of your database, so in the future, continue to optimize WordPress database time to time.
Oskar Edin runs a web development company in Sweden, Northern Web, where he constantly works and learns new things which he later on shares with the community, here at CodeEnlightened.com. When he is not working you probably find him in his car driving around or hanging around with friends.