dirtless databases - how to keep your wordpress database squeaky clean

Spring Cleaning Your Site: Dirtless Databases

How to Keep Your WordPress Database Squeaky Clean

Adam Soucie

• Highforge

• Web Developer

• Content writer

• All of your site’s data

• Pages, posts, comments, etc.

• You don’t have a site without it

What is WordPress Database?

4 Easy Tips

1. Optimize using MySQL

2. Repair broken tables

3. Prevent/limit revisions

4. Delete excess junk

• Make a database backup

• Spend 10 minutes reviewing your site

• Set up a development environment if possible

BEFORE YOU BEGIN

Optimizing using MySQL

1. Log in to PHPMyAdmin

2. Select “Check Tables Having Overhead”

3. Select “Optimize Table”

• Can be done through PHPMyAdmin

• WordPress will repair automatically as of 2.9

• Must be enabled in wp-config.php

Repairing Broken Tables

define( 'WP_ALLOW_REPAIR', TRUE );

Source: http://premium.wpmudev.org/blog/wordpress-maintenance-101-how-to-optimize-and-repair-database-tables/

Prevent/Limit Revisions

1. Different from autosaves

2. Revisions created every

time you save

3. Can be limited or

completely prevented

4. Handled in

wp-config.php

define( 'WP_POST_REVISIONS', X );

where X is the number of revisions or

‘TRUE’ to allow unlimited

Source: http://codex.wordpress.org/Revisions

• Expired transients

• Spam and unapproved comments

• Trashed items (posts, pages, etc.)

Cleaning Excess Junk

define( ‘EMPTY_TRASH_DAYS’, X );

where X is the number of days

Source: http://www.wpexplorer.com/optimize-wordpress-database/

The Transients Debate

1. Created with the Transient API

2. Supposed to expire and be deleted

3. Relied upon by lazy developers

4. WordPress only wipes them on Database Upgrades

The Plugins

1. WP-Optimize

2. purge-transients https://github.com/Seebz/Snippets/tree/master/Wordpress/

plugins/purge-transients

Any questions?

Sources:

The WordPress Codex WP Tavern (wptavern.com)

WP Explorer (wpexplorer.com) Stu Miller (stumiller.me)

More info:

www.adamsoucie.com www.highforge.com

Illustrations by:

Tina Fiume