With page load time becoming an increasingly important factor in SEO, making sure your site is optimized is extremely important. Fractions of a second could be a major negative factor when it comes to where you rank in search engines, especially when it comes to mobile usage.
So today, I thought I would give you a quick WordPress tip to lighten up your database to make sure your queries can run as quickly as possible. WordPress keeps a log of every revision you make (even auto-saved revisions). If you write long posts and write often, you could have thousands or even tens- or hundreds-of-thousands of revisions in your database! How often do you actually go back and use these revisions? Not often! So why are they clogging up your database! Let’s get rid of them!
Admittedly, this trick will be of much more use to very large sites (hundreds or thousands of posts), but smaller sites could see noticeable improvements, especially on slower shared hosting environments.
So let’s get started.
STOP! Backup your database!
Before you ever run a destructive SQL query, you want to make sure that you back up your database. If for some reason something goes amiss, you have the data to repopulate your database right there. If you do not backup your database (it’s a good practice to do this regularly), and something goes wrong, you are out of luck – because the query I’m about to show you removes rows from the database that you cannot get back.
Open up your servers database administration tool of choice. For me personally, I use phpMyAdmin.
DELETE a,b,c FROM pre_posts a LEFT JOIN pre_term_relationships b ON (a.ID = b.object_id) LEFT JOIN pre_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'
Note: replace ‘pre_’ with your database’s table prefix if you have one. In my case, the prefix is ‘bsk_’ but others such as ‘wp_’ are very common.
By running this query, you are removing all instances of your post “revisions” in your WordPress database. This can significantly reduce table size and query run-time.
Take this site for example. At this very moment I have 1653 rows in my
wp_posts table. After running this query, I get notification that 1200 of them were removed. That is a reduction of 72%, not bad! To put this in more quantitative terms, I backed up the
wp_posts table before running this query, and the backup.sql file was 4.39 MB. After I ran the query, I exported the same table, but this time the .sql file was only 657 KB. That is quite the improvement!
There is your WordPress Quick Tip for the day!