In this article, we will perform a mini-optimization of the database that can speed up the loading of the admin part of WordPress.
Revisions
The first thing we will do is delete the post revisions, which allow you to see the changes you made in WordPress. We conducted a mini-survey with our clients, and 70% didn’t even know they had this feature, while the remaining 30% knew about it but never looked at any past changes.
This is a useful option provided by WordPress that allows you to view and correct past errors if needed. However, this applies only to those who do not use this option.
Every change you make and save with “update” gets stored as an original post.
We will turn off this option by adding the following line to the wp-config.php file:
define('WP_POST_REVISIONS', false);
You can add this line anywhere in the file. This will disable any revisions in WordPress, but if you need this feature, you can define how many versions to save with the following code:
define('WP_POST_REVISIONS', 3);
By setting the limit to 3, you may have a better option since you never know when you’ll need to review a previous version. However, if you’re sure you don’t need this feature, simply turn it off to significantly reduce the database weight.
Autosave
One option that will help reduce the load on your hosting account and speed up your site is to increase the autosave interval when writing a post.
Autosave is currently set to 60 seconds by default, which means your post is saved every 60 seconds, and it writes to the database every 60 seconds. This, combined with visitor traffic and multiple editors working, can cause a lot of database reads and writes, increasing CPU usage on the hosting and potentially causing problems on hosts with CPU limits enforced by CloudLinux.
To define the autosave interval, add the following line to the wp-config.php file:
define('AUTOSAVE_INTERVAL', 300 );
By doing this, you have set the autosave interval to 300 seconds instead of the default 60 seconds.
Deleting Revisions
After limiting or disabling the new entries and duplications, we now need to delete the previous revisions. We will do this by logging into phpMyAdmin (log into cPanel (yourdomain.com/cpanel), then find the phpMyAdmin icon). It will open in a new tab, and you will see the database on the left side; click on it with the left mouse button.
You can find plugins that doing the same thing if you are not comfortable to do it on this way.
After that, the table will be listed on the left side in the gray area where you clicked, and on the right side, you will see options like “Structure,” “SQL,” and “Search.” Click on “SQL.”
Then, enter the following in the input field:
DELETE FROM [Prefix]_posts WHERE post_type = “revision”;
Click the “Go” button in the lower-right corner.
You have now deleted the revisions. Great!
Optimization
The last thing we will do regarding optimization is to use the “Optimize” option in phpMyAdmin, which also helps reduce the database weight.
Open phpMyAdmin in the same way as in the previous step.
Click on your database in the right pane to list the tables on the right side. At the bottom, you have “Check all,” and right next to it on the right side, click on “With selected” and choose “Optimize table.” When you click on it, the application will automatically optimize the table; wait for it to finish.
Once done, you will see the database size at the bottom. In our test case, it was 6.4MB and 3.4MB before optimization, and after optimization, it was 2.8MB and 0B (this means zero bytes, which means nothing).
You will see for yourself how much the database size actually decreases; the differences are very, very significant.
As you can see, it wasn’t difficult at all, and there was no need for any plugin that ADDITIONALLY burdens your site or database and then creates a counter-effect.
Avoid plugins as much as possible because they further burden your site.
