Your WordPress database is the engine behind every page load. It stores posts, pages, comments, user data, plugin settings, transients, and much more. Over time, databases accumulate bloat — post revisions, spam comments, orphaned metadata, expired transients, and fragmented tables — all of which slow down queries and degrade your site's performance. This guide covers how to clean, optimize, and maintain your WordPress database on SakuraHost.

Understanding Your WordPress Database

A standard WordPress installation creates 12 core tables (with the default wp_ prefix):

  • wp_posts — all content (posts, pages, revisions, attachments, custom post types)
  • wp_postmeta — metadata for posts (custom fields, plugin data)
  • wp_options — site settings, plugin options, transients
  • wp_comments / wp_commentmeta — comments and their metadata
  • wp_users / wp_usermeta — user accounts and profiles
  • wp_terms / wp_termmeta / wp_term_taxonomy / wp_term_relationships — categories, tags, and taxonomies
  • wp_links — blogroll links (legacy, rarely used)

Plugins often add their own tables. A site with 20+ plugins may have 50-100 database tables. Learn more at the WordPress Developer Reference.

Common Sources of Database Bloat

Post Revisions

WordPress saves a revision every time you click Save Draft or Update. A post edited 50 times has 50 revisions stored in wp_posts, each with corresponding rows in wp_postmeta. On a busy blog, this can add thousands of unnecessary rows.

Transients

Transients are temporary cached data stored in wp_options by plugins and themes. Expired transients should be cleaned up automatically, but many plugins leave orphaned transients behind. A bloated wp_options table is one of the most common causes of slow WordPress queries because WordPress loads autoloaded options on every page request.

Spam and Trashed Comments

Spam comments and items in the trash consume database space. WordPress does not automatically purge the trash until 30 days have passed.

Orphaned Metadata

When you delete a post or comment, the corresponding metadata in wp_postmeta or wp_commentmeta sometimes remains as orphaned rows with no parent.

Plugin Leftovers

Many plugins do not clean up after themselves when deactivated or deleted. Custom tables, options, and transients may remain in your database indefinitely.

Method 1: WP-Optimize Plugin

WP-Optimize is a comprehensive database optimization plugin that handles cleaning, table optimization, and scheduled maintenance.

Install and Run WP-Optimize
  1. Install from Plugins > Add New and activate
  2. Go to WP-Optimize > Database
  3. Review each optimization option:
    • Clean all post revisions
    • Clean all auto-draft posts
    • Clean all trashed posts
    • Remove spam and trashed comments
    • Remove expired transients
    • Clean orphaned post/comment metadata
    • Optimize database tables
  4. Click Run all selected optimizations
Schedule Automatic Optimization

In WP-Optimize > Settings, enable scheduled cleanups. We recommend weekly database optimization and monthly table optimization. This prevents bloat from accumulating.

Method 2: Advanced Database Cleanup via phpMyAdmin

For more control, you can optimize your database directly through phpMyAdmin in your SakuraHost cPanel.

Always back up your database before running manual SQL queries. Export your database via phpMyAdmin > Export before making any changes. A single mistake in a DELETE query can destroy your content permanently.
Delete Post Revisions
DELETE FROM wp_posts WHERE post_type = 'revision'; -- Also clean orphaned postmeta for deleted revisions DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
Remove Expired Transients
DELETE FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP(); DELETE FROM wp_options WHERE option_name LIKE '_transient_%' AND option_name NOT LIKE '_transient_timeout_%' AND option_name IN ( SELECT CONCAT('_transient_', SUBSTRING(option_name, 21)) FROM (SELECT option_name FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()) AS expired );
Optimize All Tables

In phpMyAdmin, select your WordPress database, click Check all at the bottom, choose Optimize table from the dropdown, and execute. This defragments tables and reclaims wasted space. Equivalent SQL:

-- Optimize specific large tables OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments, wp_commentmeta;

Method 3: WP-CLI Database Optimization

If you have SSH access to your SakuraHost account, WP-CLI provides powerful database management:

# Optimize all database tables wp db optimize # Delete all post revisions wp post delete $(wp post list --post_type='revision' --format=ids) --force # Search for large autoloaded options wp db query "SELECT option_name, LENGTH(option_value) AS size FROM wp_options WHERE autoload='yes' ORDER BY size DESC LIMIT 20;" # Delete expired transients wp transient delete --expired

Controlling Post Revisions

Prevent future bloat by limiting revisions. Add to wp-config.php:

/* Limit to 5 revisions per post */ define( 'WP_POST_REVISIONS', 5 ); /* Or disable revisions entirely (not recommended for most sites) */ /* define( 'WP_POST_REVISIONS', false ); */ /* Empty trash after 7 days instead of 30 */ define( 'EMPTY_TRASH_DAYS', 7 );

Optimizing the wp_options Table

The wp_options table is unique because WordPress loads all rows with autoload='yes' on every single page request. A bloated autoload set is a common hidden performance killer.

Audit Autoloaded Data

Run this query in phpMyAdmin to see total autoloaded data size:

SELECT SUM(LENGTH(option_value)) AS autoload_size FROM wp_options WHERE autoload = 'yes';

If this exceeds 1MB, you have optimization opportunities. Identify the largest autoloaded entries:

SELECT option_name, LENGTH(option_value) AS size FROM wp_options WHERE autoload = 'yes' ORDER BY size DESC LIMIT 25;

Large entries from deactivated or deleted plugins can safely have their autoload set to 'no' or be deleted entirely.

Database Optimization Schedule

We recommend this maintenance schedule for WordPress sites on SakuraHost:

  • Weekly: Clean transients, spam comments, and trashed items (automate with WP-Optimize)
  • Monthly: Optimize database tables and review autoloaded options size
  • Quarterly: Audit installed plugins for orphaned tables, clean post revisions beyond the limit
  • Before major updates: Full database backup and optimization

Monitoring Database Performance

Install Query Monitor to see exactly which database queries are running on each page, how long they take, and which plugin or theme triggers them. This is invaluable for identifying slow queries that need optimization or plugins that are making excessive database calls.

Database Performance Issues? If your WordPress site on SakuraHost is experiencing slow database queries despite optimization, it may be time to consider upgrading to a plan with more resources. Contact our team at billing.sakurahost.co.tz/submitticket.php for a performance review and hosting recommendation.

For more on WordPress database structure and optimization, see the WordPress Developer Reference and the MySQL OPTIMIZE TABLE documentation.

Was this answer helpful? 0 Users Found This Useful (0 Votes)

Most Popular Articles

How to Install WordPress on SakuraHost Using Softaculous

.kb-article { line-height: 1.8; color: inherit; max-width: 900px; } .kb-article h2 { color:...
Essential WordPress Security: Protecting Your Site from Hackers

.kb-article { line-height: 1.8; color: inherit; max-width: 900px; } .kb-article h2 { color:...
How to Speed Up Your WordPress Website on SakuraHost

.kb-article { line-height: 1.8; color: inherit; max-width: 900px; } .kb-article h2 { color:...
WordPress Backup Guide: Protecting Your Content and Database

.kb-article { line-height: 1.8; color: inherit; max-width: 900px; } .kb-article h2 { color:...
How to Set Up WordPress Multisite on SakuraHost

.kb-article { line-height: 1.8; color: inherit; max-width: 900px; } .kb-article h2 { color:...