Optimizing the WP Database

Optimizing the WP databaseEvery time I edit a post or page, WP keeps a copy of the old version in my database. It is a great feature, handy when I mess up and need to revert to the previous version. But once my post or page is final, I have no use for the prior revisions. By default, WP keeps all the old versions, forever, and they can add up over time. I recently checked one of my sites and was surprised to find 3,566 useless old pages cluttering up my database. A large, cluttered database slows my site, as the server takes longer to retrieve information.

I should always make sure I have an up-to-date backup of my site. This is especially true before doing any database maintenance. I never know when something could go horribly wrong.

To clean out old posts and pages, as well as other database clutter, I can use a popular free plugin called WP-Optimize. Once all the clutter is removed, WP-Optimize can defragment/compact the database tables for optimum efficiency. Since my site is on a LiteSpeed server I could also use similar functionality provided by the excellent LiteSpeed Cache plugin.

I can limit the number of future page and post revisions that are saved by adding a line to wp-config.php. In my case, I save the latest two revisions: define('WP_POST_REVISIONS', 2);

Update (2018-07-18):  Another cause of database clutter is deleted plugins. A well-behaved plugin should clean up after itself – giving me the option of removing all its data from my database when I delete it. Many plugins are not so polite. Deleting the plugin removes only the files, leaving now-useless database tables behind. A tool that can help with this is Plugins Garbage Collector. After making sure I have an up-to-date DB backup, I can run PGC to search my DB for tables left behind by deleted plugins, then remove those tables.

WPPOV supports freedom from Net Neutrality and the GDPR. The Internet of the people, by the people, for the people, shall not perish from the Earth.