How to Clean a WordPress Database: SQL & PHP Guide

Hi! In this post, I’ll show you how to clean up a WordPress database by removing unnecessary data with SQL queries and PHP code.

Delete all temporary options (transients)

DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient_%');
DELETE FROM `wp_options` WHERE `option_name` LIKE ('\_transient\_%');
DELETE FROM `wp_options` WHERE `option_name` LIKE ('_site_transient_%');

Delete all existing revisions

// Method 1

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision' AND post_name LIKE '%revision%');
DELETE FROM wp_term_relationships WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type = 'revision' AND post_name LIKE '%revision%');
DELETE FROM wp_posts WHERE post_type = 'revision' AND post_name LIKE '%revision%';

// Method 2

DELETE a,b,c,d
	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)
		LEFT JOIN wp_comments d ON (a.ID = d.comment_post_ID)
	WHERE a.post_type = 'revision'

You can also delete revisions with the following code in your functions.php file:

/**
 * Manual cleanup of revisions (analog of SQL query)
 * Run once via: /wp-admin/?cleanup_revisions=1
 */
add_action( 'admin_init', function() {
    if (
        ! current_user_can( 'administrator' ) ||
        ! isset( $_GET['cleanup_revisions'] ) ||
        $_GET['cleanup_revisions'] != '1'
    ) {
        return;
    }

    global $wpdb;

    // Find all revision IDs
    $revision_ids = $wpdb->get_col( "
        SELECT ID
        FROM {$wpdb->posts}
        WHERE post_type = 'revision'
        AND post_name LIKE '%revision%'
    " );

    if ( empty( $revision_ids ) ) {
        wp_die( 'No revisions found.' );
    }

    $ids = implode( ',', array_map( 'intval', $revision_ids ) );

    // Delete metadata
    $wpdb->query( "
        DELETE FROM {$wpdb->postmeta}
        WHERE post_id IN ($ids)
    " );

    // Delete term relationships
    $wpdb->query( "
        DELETE FROM {$wpdb->term_relationships}
        WHERE object_id IN ($ids)
    " );

    // Delete the revisions
    $wpdb->query( "
        DELETE FROM {$wpdb->posts}
        WHERE ID IN ($ids)
    " );

    wp_die( 'Revisions cleaned: ' . count( $revision_ids ) );
} );

To run the script, open https://site.com/wp-admin/?cleanup_revisions=1. After the revisions have been deleted successfully, you’ll see a message similar to Revisions cleaned: XXX. Remove or comment out the script after use, and add it again only when you need it.

Delete all post meta with a specific key

The delete_post_meta() function deletes metadata only for a specified post. To delete a custom field from every post, add the following PHP code to your functions.php file.

global $wpdb;
$meta_key = 'meta_field_name';
$deleted_db_rows = $wpdb->query(
	$wpdb->prepare( "DELETE FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key )
);
echo $deleted_db_rows;

Replace meta_field_name with the name of the meta key you want to delete. Remove or comment out this code when you’re finished.

You can also use this alternative to delete fields with a name such as meta_name:

DELETE pm FROM wp_postmeta pm WHERE pm.meta_key = 'meta_name';

Remove unused taxonomy terms

Deleting a large number of WordPress posts can leave unnecessary data in the database, such as unused tags. Use the following SQL queries to remove it:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE `count` = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id NOT IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id NOT IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

To remove only unused tags, use this query:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE taxonomy='post_tag' AND count=0 );

To optimize the tables by cleaning indexes and defragmenting unused data files, run:

OPTIMIZE TABLE `wp_terms` , `wp_term_taxonomy` , `wp_term_relationships`;

Delete orphaned post meta

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

Full-cycle WordPress website development, improvements, new features, bug fixes, maintenance, updates and migration to another domain or hosting.