General guide on cleaning up a WordPress database via SQL

[Aior]

Below is a general guide on cleaning up a WordPress database via SQL. These are common cleanup tasks—removing post revisions, spam/trashed comments, orphaned metadata, expired transients, etc. Always back up your database before running any SQL commands. Once you have a backup, you can run these queries (e.g., via phpMyAdmin or MySQL CLI) on your database named ersamach_BurDerAi.

1. Use the Correct Database

If you’re connecting via command line (or any other tool that doesn’t automatically select the DB), select the WordPress database first:

USE database_namei
If you’re using phpMyAdmin (or another GUI), just make sure you select the ersamach_BurDerAi database on the left panel before executing the queries.

2. Identify Your Table Prefix

WordPress tables typically start with wp_ by default, but they may be different for security or organizational reasons (e.g. wpxy_). Replace all occurrences of wp_ in the queries below with your actual prefix.



3. Remove Post Revisions

By default, WordPress stores every saved draft or update as a “revision.” If you do not need these, you can remove them:

DELETE a, b, c
FROM wp_posts a
LEFT JOIN wp_term_relationships b
    ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c
    ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

This query removes the revision itself (wp_posts), any term relationships (wp_term_relationships), and post meta (wp_postmeta) linked to those revisions.

4. Remove Auto-Draft Posts

WordPress auto-saves drafts periodically. If they’re old and unnecessary, you can remove them:

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

5. Clean Up Spam/Trashed Comments

Spam or trash comments can build up significantly:

-- Delete all spam comments
DELETE FROM wp_comments
WHERE comment_approved = 'spam';

-- Delete all trashed comments
DELETE FROM wp_comments
WHERE comment_approved = 'trash';

6. Remove Orphaned Comment Metadata

DELETE FROM wp_commentmeta
WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

7. Remove Orphaned Post Metadata

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

8. Remove Expired Transient Options

-- Remove all transients, both expired and active, if you no longer need them
DELETE FROM wp_options
WHERE option_name LIKE '%\_transient\_%';

9. Optimize Tables

OPTIMIZE TABLE wp_posts,
               wp_postmeta,
               wp_comments,
               wp_commentmeta,
               wp_options,
               wp_terms,
               wp_term_taxonomy,
               wp_term_relationships;

Important Notes

1. Back Up First: Always back up your WordPress database before executing direct delete or optimize operations.

2. Check Table Prefixes: Update all wp_ to match your actual table prefix if different.

3. Proceed With Caution: If you’re unsure about a query, test it on a staging copy of your site/database.

Following these steps should help you clean and optimize your WordPress database named ersamach_BurDerAi. Once finished, your database will typically be smaller and may run faster.