Deleting all WooCommerce products quickly using SQL statements
If you are testing my Super Speedy Imports plugin, or for whatever reason, sometimes you just wish to delete all your products quickly.
If you have millions of products, deleting them through the wp-admin interface will take days. Instead, deleting them through direct SQL commands will take minutes.
Requirements and things to know
You must be able to run SQL commands against your database. This can be through PHPMyAdmin, through SSH or the SQL Executioner plugin.
You should take a database backup before running this.
No do_action calls will be executed with this code. I’ve added a couple of extra commands at the end to wipe out the products from the Super Speedy Search tables and the Super Speedy Filters object ancestors table.
SQL Statements to delete all your products quickly
To do this properly, we need to delete from the leaf tables first and the wp_posts table last so we know we are always deleting ‘product’ entries.
DELETE tm FROM wp_termmeta AS tm
INNER JOIN wp_term_taxonomy AS tt ON tm.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_posts AS p ON tr.object_id = p.ID
WHERE p.post_type IN ('product', 'product_variation');
DELETE t FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_posts AS p ON tr.object_id = p.ID
WHERE p.post_type IN ('product', 'product_variation');
DELETE tt FROM wp_term_taxonomy AS tt
INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
INNER JOIN wp_posts AS p ON tr.object_id = p.ID
WHERE p.post_type IN ('product', 'product_variation');
DELETE FROM wp_term_relationships
WHERE object_id IN (
SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);
DELETE FROM wp_postmeta
WHERE post_id IN (
SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);
DELETE pm FROM wp_postmeta pm
INNER JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'attachment'
AND p.post_parent IN (
SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);
DELETE FROM wp_posts
WHERE post_type = 'attachment'
AND post_parent IN (
SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);
DELETE FROM wp_posts WHERE post_type IN ('product', 'product_variation');
Deleting entries in wp_superspeedysearch:
DELETE FROM wp_superspeedysearch WHERE post_type IN ('product', 'product_variation');
Emptying wp_fww_object_ancestors:
DELETE from wp_fww_object_ancestors WHERE post_type IN ('product', 'product_variation');
