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.

htaccess tricks

htaccess tricks improve WP securityAlong with the excellent 6G firewall from Jeff Starr at Perishable Press, certain htaccess tricks improve WP security. Examples …

  • Block directory browsing:
    Options -Indexes
  • Restrict access to wp-config.php:
    <files wp-config.php>
    order allow,deny
    deny from all
  • Restrict access to .htaccess:
    <Files .htaccess>
    order allow,deny
    deny from all
  • Restrict access to install.php:
    <Files install.php>
    order allow,deny
    deny from all
    < /Files>

Perishable press offers many more stupid htaccess tricks.

Keys and Salts

change my WP keys and saltsWordPress uses a cookie to keep track of my login state. While the technical details are a bit out of my comfort zone, if an attacker gets his or her hands on or forges my admin authentication cookie, he or she could take over my admin role and cause a great deal of mischief.

I can easily make my authentication cookie much more secure using keys and salts stored in my wp-config.php file. Google can easily find details and instructions in a number of articles, including All You Need To Know On the WordPress Unique Authentication Keys and Salts.

It seems a reasonable security precaution to change my WP keys and salts periodically – annually seems about right for me. I just need to remember that doing so will cause anyone logged into my site to get booted out and have to log in again.

Update: I stumpled onto the excellent Salt Shaker plugin. It updates WP security keys and salts automatically, every month. I could also choose every week or every day, but that seems like overkill. I now use Salt Shaker on all my WP sites.