Optimize WordPress database

Your WordPress database grows every day. Old revisions, spam comments and transients fill the database. This makes your site slower. With regular database maintenance you keep WordPress fast and efficient.

In this guide you'll learn how to optimize your WordPress database. From manual optimization to automatic maintenance with plugins.

Why optimize database?

The WordPress database stores everything: posts, pages, comments, settings and more. After months of use a lot of unnecessary data accumulates.

Problems of a full database:

• Slower queries and load times
• More server resources needed
• Slower backups
• Higher hosting costs with databases with quota
• Worse performance when searching

A clean database is faster. Queries find data easier in a cleaned up structure.

What accumulates in a database?

• Post revisions (every edit is saved)
• Spam and trash comments
• Expired transients (temporary data)
• Post meta from deleted plugins
• Auto-drafts and unused tables

Regular cleanup prevents your database from becoming unnecessarily large. This significantly improves performance.

Use phpMyAdmin

phpMyAdmin is a free tool for database management. Most hosting providers offer phpMyAdmin by default.

Access to phpMyAdmin:

1. Log in to your hosting control panel
2. Look for phpMyAdmin (often under Databases)
3. Select your WordPress database
4. Now you see all database tables

Create database backup: Before you start optimizing make a backup:

1. Click on your database name
2. Go to "Export" tab
3. Choose "Quick" export method
4. Select "SQL" format
5. Click "Go" to download

Save this backup safely. In case of problems you can restore the database.

Optimize tables:

1. Select all tables (check checkbox)
2. Scroll down to "With selected"
3. Choose "Optimize table"
4. Click "Go"

This reorganizes the data and removes fragmentation. Especially useful after many delete actions.

Check overhead: Tables with overhead waste space. In phpMyAdmin you see the overhead column for each table. Optimize tables with overhead to reclaim space.

Use WP-Optimize plugin

WP-Optimize is a popular free plugin for database optimization. It's safer and easier than working manually.

Install WP-Optimize:

1. Go to Plugins → Add New
2. Search "WP-Optimize"
3. Install and activate the plugin
4. Go to WP-Optimize in your admin menu

Perform basic optimizations: WP-Optimize shows various optimization options:

• Optimize database tables
• Clean up post revisions
• Remove auto-draft posts
• Remove spam and trash comments
• Remove expired transients
• Remove pingbacks and trackbacks

Check the desired options and click "Run optimization". The plugin shows how much space you save.

Schedule automatic optimization: WP-Optimize can automatically optimize weekly:

1. Go to Settings tab
2. Enable "Automatic weekly clean-up"
3. Choose which optimizations run automatically
4. Save

For busy sites you can set this daily. For small blogs weekly is enough.

Optimize settings: Under Settings → Database you can configure more:

• Limit revisions (e.g. max 5 per post)
• Disable auto-drafts
• Automatically remove unapproved comments

Clean up post revisions

WordPress saves every change to posts and pages. After months of writing you have hundreds of revisions.

Remove revisions manually: Via phpMyAdmin:

DELETE FROM wp_posts WHERE post_type = 'revision';

Note: wp_ is the default table prefix. Check your prefix in wp-config.php.

With WP-Optimize: Select "Clean post revisions" and run. You can set how many revisions you want to keep.

Limit future revisions: In wp-config.php:

define('WP_POST_REVISIONS', 5);

This limits revisions to maximum 5 per post. Or disable completely:

define('WP_POST_REVISIONS', false);

For most sites 3-5 revisions are sufficient. More is usually not needed.

Remove spam comments

Spam comments fill your database quickly. Even with Akismet spam comments accumulate in the spam folder.

Manually via WordPress admin:

1. Go to Comments
2. Click on "Spam" tab
3. Select all spam comments
4. Choose "Delete permanently"

This works but takes long with thousands of spam comments.

Via phpMyAdmin:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Automatically with WP-Optimize: Select "Remove spam and trashed comments" and run. This removes all spam and trash at once.

Prevent spam:

• Use Akismet or other anti-spam plugin
• Disable comments on old posts
• Use CAPTCHA on comment forms
• Moderate comments before they appear

Remove transients

Transients are temporary data that WordPress and plugins store. These have an expiration date but sometimes stay longer.

What are transients? Transients cache temporary data like API responses, widget output or query results. This speeds up your site.

Problem with transients: Expired transients are not always automatically removed. They fill the wp_options table.

Remove transients with WP-Optimize: Select "Remove expired transients" and run. This only removes expired transients, active ones stay.

Manually via phpMyAdmin:

DELETE FROM wp_options WHERE option_name LIKE '_transient_%';

Note: this removes all transients, including active ones. Plugins regenerate them when used.

Best practice: Only remove expired transients unless you have problems. Removing active transients has little use because they come back.

Clean up post meta

Post meta stores extra information with posts. Plugins often add post meta. When you remove a plugin the meta data often stays.

Find orphaned post meta: This is meta from posts that no longer exist:

DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);

Remove duplicates: Sometimes duplicate meta entries occur:

DELETE FROM wp_postmeta WHERE meta_id NOT IN (
  SELECT MIN(meta_id) FROM wp_postmeta GROUP BY post_id, meta_key
);

Note: test SQL queries first on staging. Wrong queries can delete data you need.

Plugin specific meta: If you remove a plugin check if it leaves meta data. Search in wp_postmeta for meta_key with the plugin name.

Remove auto-drafts and trash

WordPress saves auto-drafts while writing. These sometimes stay after publishing.

Remove auto-drafts:

DELETE FROM wp_posts WHERE post_status = 'auto-draft';

Remove trash posts: Posts in trash stay for 30 days:

DELETE FROM wp_posts WHERE post_status = 'trash';

Lower trash retention: In wp-config.php:

define('EMPTY_TRASH_DAYS', 7);

Now trash is automatically emptied after 7 days instead of 30.

Remove unused tables

Plugins sometimes create custom database tables. After removing plugins these tables often stay.

Find unused tables: In phpMyAdmin you see all tables. WordPress core tables start with wp_:

• wp_posts
• wp_postmeta
• wp_users
• wp_usermeta
• wp_comments
• wp_commentmeta
• wp_terms
• wp_term_relationships
• wp_term_taxonomy
• wp_termmeta
• wp_options

Tables with other names are from plugins. Check if the plugin is still active before removing.

Remove table: Select the table and click "Drop". Note: this is permanent and cannot be undone.

Backup first: Export the table before removing. If you need it later you can import.

Repair database

With crashes or corruption you can repair the database.

Via wp-config.php: Add to wp-config.php:

define('WP_ALLOW_REPAIR', true);

Visit: http://example.com/wp-admin/maint/repair.php

Choose "Repair Database" or "Repair and Optimize Database". Remove the define line after use because the page is publicly accessible.

Via phpMyAdmin: Select damaged tables and choose "Repair table" at "With selected".

Note: only repair if you have database errors. For regular maintenance optimize is better.

Schedule regular maintenance

One-time optimization helps, but regular maintenance is better.

Weekly:

• Remove spam comments
• Clean up expired transients
• Remove auto-drafts

Monthly:

• Optimize database tables
• Clean up post revisions
• Remove orphaned post meta

Quarterly:

• Check unused tables
• Create database backup
• Measure performance before and after

Automate with WP-Optimize: Set up automatic weekly optimization. This runs in the background without you thinking about it.

Set up cron job: For advanced users: create a server cron job that uses WP-CLI:

wp db optimize --path=/var/www/html

This is faster than plugins and uses no WordPress resources.

Monitor database size

Keep an eye on your database size to spot problems early.

Check size in phpMyAdmin: At your database name you see the total size. Also check per table to see what uses the most space.

Via WP-CLI:

wp db size --tables

This shows the size per table.

Alarm at large growth: If your database suddenly grows a lot something is wrong. Check which table grows and investigate why.

Expected sizes:

• Small blog (< 100 posts): 5-20 MB
• Medium site (1000 posts): 50-100 MB
• Large site (10000+ posts): 200-500 MB
• Webshop (WooCommerce): 100-1000 MB

Much larger? Then there's probably unnecessary data.

Database hosting considerations

With growing databases your hosting choice becomes important.

Shared hosting limits: Shared hosting often has database size limits. Check your quota and upgrade if needed.

VPS or dedicated for large databases: At 500+ MB database VPS hosting or dedicated is often better. You get dedicated database resources.

Managed WordPress hosting: Providers like Kinsta or WP Engine optimize databases automatically. This saves maintenance.

Database caching: Redis or Memcached cache database queries. This significantly speeds up sites with large databases.

Best practices

Always make a backup: before optimizing the database. A mistake can delete data.

Test on staging: test optimizations first on a staging site. Not directly on production.

Don't optimize too often: daily is overkill for small sites. Weekly or monthly is enough.

Monitor performance: measure load times before and after optimization. Check if it really helps.

Watch dependencies: some plugins expect old data. Test functionality after cleanup.

Use WP-CLI: for large databases WP-CLI is faster than plugins or phpMyAdmin.

Advanced optimization tips

Optimize InnoDB: WordPress uses InnoDB engine. This fragments less but optimize regularly:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options;

Add index for faster queries: On busy sites extra indexes can help:

CREATE INDEX meta_key_value ON wp_postmeta(meta_key, meta_value(10));

Note: too many indexes slow down INSERT and UPDATE queries.

Query cache: Check if query cache is on:

SHOW VARIABLES LIKE 'query_cache%';

With some hosts you can set this yourself in my.cnf.

By regularly optimizing your WordPress database you keep your site fast and efficient. Plan regular maintenance and automate where possible. With a clean database your site loads faster and uses fewer server resources.

Frequently Asked Questions

Is WordPress free?

WordPress itself is free open-source software. You only pay for hosting, a domain name, and any premium themes or plugins you want to use.

How difficult is WordPress to learn?

WordPress is relatively easy to learn. You can master the basic functions within a few hours. Advanced customizations require more time.

Can I move WordPress to a different host later?

Yes, WordPress websites can be moved to a different hosting provider. Most providers offer free assistance for this.