WordPress Database Optimization & Cleanup

Introduction

With just a few simple clicks, using your hosting control panel and popular plugins like WP Rocket and WP-Optimize, you can make your database lighter and, as a result, noticeably boost your site’s speed.

What is a WordPress Database? 🤔

It’s a database that stores all your site’s information in different tables. Every time you write a post, install a plugin, or change a setting, all that important info—from posts and comments to settings and data—fills up these tables.

But here’s the catch: over time, a bunch of unnecessary data, like old post revisions, tables from deleted plugins, or temporary data, piles up in your database. This makes your database heavy and slows down your site.

Optimization database tools

Here are some useful tools for checking query speed and database size on WordPress sites, listed by title:

• WP-Optimize (plugin) View table size and automatic optimization
• WordPress Dashboard: Through Site Health / Server Specifications
• phpMyAdmin: Accurately display the size of each table and run EXPLAIN commands for queries

DirectAdmin

why is database optimisation necessary?

• Reduce Unnecessary Bulk: Every time you install and delete a plugin, unused tables and rows are left behind in the database.
• Faster Data Retrieval: Scattered or bloated tables slow down queries.
• Improved Site Performance & Dashboard wordpress: Pages load faster, leading to a better user experience.

Database Types Classification

1. Under 200 MB Light
   200-۵۰۰ Medium
   500 to 2 GB Large
   Above 2 GB Heavy

Database Optimization via Your Hosting Control Panel 

The first way to optimize your WordPress database is by using your hosting control panel. It doesn’t matter if you’re using cPanel or DirectAdmin. Here are the steps for optimization with phpMyAdmin:

1. Log in to Your Host: Go to your hosting control panel and find phpMyAdmin.
2. Select Your Database: In phpMyAdmin, choose your WordPress site’s database from the list on the left.
3. Check the Tables: Take a look at the tables. Tables that start with wp_ or the name of old plugins might not be needed anymore. For example, if you deleted a plugin, its table might still be hanging around in the database.
4. Delete Unnecessary Tables: Select the tables of plugins you no longer use and delete them with the “Delete” option. For example, a “Bookly” plugin that was deleted, but its tables were still there. 😂
5. Optimize WooCommerce Tables: To optimize tables like WooCommerce’s, check the boxes for options with woocommerce or wc, and from the dropdown menu at the bottom, select “Optimize table”. ⭐

Optimize WooCommerce Tables

To optimize tables like WooCommerce, check the wocomerce or wc options and select “Optimize table” from the dropdown menu with selected- option to optimize them.⭐

• Look for WooCommerce settings in wp-option
• If you installed WooCommerce by mistake or no longer need it. Just delete all WC related tables

Database Optimization with the WP Rocket Plugin for Quick

This is an amazing plugin for WordPress database optimization that has a dedicated section for this purpose. This method is super simple and perfect for those who don’t want to do things manually.

Steps for Optimization with WP Rocket:

1. Install the Plugin: Install and activate WP Rocket from its official website.
2. Go to Settings: In your WordPress dashboard, go to the WP Rocket menu.
3. Clean the Database: In the “Database” tab, you’ll see options like cleaning up revisions, auto drafts, and spam comments. Check the ones you need.
4. Start Optimization: Click “Save Changes” to let WP Rocket do its thing.

Optimization with the WP-Optimize Plugin

If you’re looking for a more professional way to optimize your WordPress database, WP-Optimize is your best bet. This plugin has more features and can give your database a thorough cleaning.

Steps for Optimization with WP-Optimize:

1. Install the Plugin: Install and activate WP-Optimize from the WordPress repository.
2. Go to Settings: In your dashboard, go to the WP-Optimize menu.
3. Clean and Optimize: In the “Optimization” tab, check options like cleaning revisions, optimizing tables, and removing extra data (all options).
4. Run Operations: Click “Run all optimizations” to let the plugin start working.
5. Check Old Tables: In the “Tables” tab, you can see the tables of inactive plugins and delete them if they’re not needed.

Whats the Best Time for a Database Cleanup?

Optimizing your WordPress database isn’t a one-time thing; you need to make it a habit!

• Regular Schedule: Check and optimize your database monthly or every two months.
• Remove Extra Plugins: Delete any plugins you don’t need to keep your database from getting cluttered with tables.

The Golden Rule of Database Optimization

ALWAYS back up before you start!!!!!

• If you have a large site or limited resources, you can run this process during off-peak hours (at night or when traffic is low).
• Always use the latest versions of plugins and PHP; new versions are often more optimized.
• After optimizing, check your site’s loading speed with tools like GTmetrix or Google PageSpeed.

Manual Database Optimization (No Plugins)

phpMyAdmin Select the relevant database. How to run in phpMyAdmin
After logging in to phpMyAdmin and selecting the database, click on the SQL tab.
Enter one of the code blocks above in the box

Delete Post Revisions

DELETE FROM `wp_posts`
WHERE `post_type` = ‘revision’;

Limit the number of saved revisions

Setting in the wp-config.php file
In DirectAdmin, go to the File Manager.
Open the public_html (or WordPress root) folder.
Edit the wp-config.php file and add this above the line /* That’s all, stop editing! Happy blogging. */:

// Limit revisions to 3
define( ‘WP_POST_REVISIONS’, 3 );

Clean up unused tags (tags with zero count)

DELETE t
FROM `wp_terms` AS t
INNER JOIN `wp_term_taxonomy` AS tt ON t.term_id = tt.term_id
WHERE tt.taxonomy = ‘post_tag’ AND tt.count = 0;

Remove empty or null metadata (Post Meta)

DELETE pm
FROM `wp_postmeta` AS pm
LEFT JOIN `wp_posts` AS p ON pm.post_id = p.ID
WHERE p.ID IS NULL
OR pm.meta_value IS NULL
OR pm.meta_value = ”;

Remove old and unused custom post types

For example, remove WooCommerce coupons

DELETE FROM `wp_posts`
WHERE `post_type` = ‘shop_coupon’;

Optimize Comments and Related Content

Clean up Spam Comments

DELETE FROM `wp_comments`
WHERE `comment_approved` = ‘spam’;

Disable and Remove Pingbacks and Trackbacks

In your WordPress dashboard, go to Settings → Discussion.
Uncheck the “Allow Pingbacks and Trackbacks” checkbox and save.
In phpMyAdmin, for previous deletions, run:

DELETE FROM `wp_comments`
WHERE `comment_type` IN (‘pingback’,’trackback’);

Remove Empty or Null Comment Metadata

DELETE cm
FROM `wp_commentmeta` AS cm
LEFT JOIN `wp_comments` AS c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;
Delete Old WooCommerce Order Notes
DELETE FROM `wp_comments`
WHERE `comment_type` = ‘order_note’;

General Optimizations and Settings

Setting Up Automatic Trash Emptying
By default, WordPress empties the trash after 30 days. To change this period, add this to wp-config.php:

// Automatically empty the trash after 7 days
define( ‘EMPTY_TRASH_DAYS’, 10 );

https://mrbarati.com/category/wps/