A fast database is essential for a smooth WordPress website. After months or years of use, your database fills up with unnecessary data: revisions, spam comments, temporary options, and deleted content. In this article, you will learn how to clean up and optimize your WordPress database for better performance.

Connecting to phpMyAdmin

phpMyAdmin is the standard tool for database management on web hosting. At Theory7, you can access phpMyAdmin via DirectAdmin:

1. Log into DirectAdmin via yourdomain.com:2222 or via the MyTheory7 customer panel
2. Go to "MySQL Management" under the heading "Your Account"
3. Click on "phpMyAdmin" next to the database you want to manage
4. You are automatically logged into phpMyAdmin

More information about phpMyAdmin can be found in our article What is phpMyAdmin?

Make a backup first!

Before making changes to your database, always make a backup first. This is crucial - a mistake in the database can make your entire website unusable.

Backup via phpMyAdmin

1. Select your database in the left panel
2. Click on "Export" in the top menu
3. Choose "Quick" for a standard SQL export
4. Click on "Go" to download the .sql file

Backup via plugin

If you prefer to use a plugin, install UpdraftPlus for automatic backups of both files and database.

Recognizing unnecessary data in WordPress

A typical WordPress database contains many tables. These start by default with wp_ (or a custom prefix). These are the most common sources of unnecessary data:

wp_posts - Revisions and drafts

WordPress by default saves unlimited revisions of each page and post. After a hundred edits, you also have a hundred revisions. This table often grows the fastest.

wp_postmeta - Metadata accumulation

Every plugin adds metadata to posts. When you remove a plugin, this metadata often stays behind. We call this "orphaned metadata".

wp_options - Temporary data (transients)

WordPress and plugins store temporary cache data in the options table. These transients should automatically expire, but that does not always happen correctly.

wp_comments and wp_commentmeta

Spam comments, even when in the trash, take up space. Also approved comments from years ago that no one reads anymore.

Deleted plugin tables

Many plugins create their own tables but do not clean them up when removed. Tables from plugins you no longer use can be safely deleted.

Cleaning with WP-Optimize

WP-Optimize is the most user-friendly plugin for database optimization. It is safer than working manually in phpMyAdmin and provides a clear overview.

Installation

1. Go to Plugins > Add New in WordPress
2. Search for "WP-Optimize"
3. Install and activate the plugin from "UpdraftPlus.Com, DavidAnderson"

Cleaning the database

1. Go to WP-Optimize > Database in your WordPress admin
2. You see a list of optimization options and how much space each action saves
3. Check what you want to clean:
   • Clean all post revisions
   • Clean all auto-draft posts
   • Clean all trashed posts
   • Remove spam comments
   • Remove trashed comments
   • Remove expired transient options
   • Remove orphaned post meta
4. Click on "Run all selected optimizations"

Optimizing tables

After deleting data, fragmented space remains in the tables. WP-Optimize can also optimize the tables themselves:

1. Go to the Tables tab
2. Select all tables
3. Click on "Optimize"

Manual cleaning via phpMyAdmin

For advanced users who want more control, you can execute queries directly in phpMyAdmin. Go to the SQL tab and execute these queries:

Delete revisions

DELETE FROM wp_posts WHERE post_type = "revision";

Delete orphaned postmeta

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL;

Delete expired transients

DELETE FROM wp_options 
WHERE option_name LIKE "%_transient_%" 
AND option_name NOT LIKE "%_transient_timeout_%";

Delete spam and trash comments

DELETE FROM wp_comments WHERE comment_approved = "spam";
DELETE FROM wp_comments WHERE comment_approved = "trash";

Note: Replace "wp_" with your own table prefix if you use a custom prefix. Check this first in wp-config.php.

Recognizing unused plugin tables

In phpMyAdmin, you see all tables in your database. WordPress core tables start with wp_ followed by standard names like posts, users, options, etc. Plugin tables often have the plugin name in them:

• wp_actionscheduler_* - Action Scheduler (WooCommerce)
• wp_wc_* - WooCommerce
• wp_yoast_* - Yoast SEO
• wp_wflogins - Wordfence

If you have removed a plugin but still see the tables, you can safely delete them. Select the table, click on "Drop" and confirm. Only do this if you are sure you no longer use the plugin!

Limiting revisions for the future

To prevent your database from filling up with revisions again, you can limit the number. Add this line to wp-config.php:

define( "WP_POST_REVISIONS", 5 );

This saves a maximum of 5 revisions per post. You can also use 0 to completely disable revisions, but we recommend at least 3-5 in case you want to roll something back.

Scheduling automatic maintenance

With WP-Optimize, you can schedule automatic maintenance:

1. Go to WP-Optimize > Settings
2. Enable "Enable scheduled clean-up"
3. Choose a frequency (weekly is sufficient for most sites)
4. Select which optimizations should run automatically

Combine this with automatic backups via UpdraftPlus so you always have a recent backup before optimization runs.

Checking the result

After optimizing, you can check the result:

• Compare the database size before and after in phpMyAdmin (click on your database to see the size)
• Measure the load time of your website with a speedtest tool
• Check if your website still works correctly

An optimized database can noticeably improve the load time of your WordPress site, especially for sites with lots of content or intensive use of plugins.

Do you have questions about database optimization or are you running into problems? The support team at Theory7 is happy to help. On our servers with fast SSD storage and optimized MySQL configuration, your database achieves the best performance.