How to quickly and in bulk delete WordPress products (with SQL statements)

Anyone who has run an independent website knows that deleting products from the backend is very slow, especially when dealing with hundreds of thousands or millions of data entries. Deleting too many at once can cause the site to freeze, and deleting dozens at a time is just too slow.

This led to the development of a script for quickly deleting WordPress products in bulk.

Clearing the POST table directly from the database removes the products, but their parameters, such as price, variations, and images, remain. Because these parameters are stored separately, you need to delete specific items from the POST table.

Prices, variations, images, etc., cannot be directly deleted because the website settings, including editor content, overall site UI, and policy settings, are all contained in this table. Therefore, we need SQL to delete the specified parameters.

The principle is to directly execute SQL statements in the database to delete products, variations, images, and prices in batches.

Method 1

Open the terminal and connect to the database of the product you want to delete.

mysql -u username -p -h 127.0.0.1 db_name

Then enter the password to confirm.

Note: To delete images and variations, please modify the SQL statement above.

Execute the following SQL statement:

-- Delete product metadata
DELETE pm
FROM wp_postmeta pm
JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'product';
-- Delete product reviews (if any)
DELETE c, ct
FROM wp_comments c
LEFT JOIN wp_commentmeta ct ON c.comment_ID = ct.comment_id
JOIN wp_posts p ON c.comment_post_ID = p.ID
WHERE p.post_type = 'product';
-- Delete product category relationships
DELETE tr
FROM wp_term_relationships tr
JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.post_type = 'product';
-- Delete product
DELETE FROM wp_posts
WHERE post_type = 'product';

Method 2

Open phpMyAdmin directly in the BT panel, select the data you want to delete, and click on the SQL statement.

Note: To delete images and variations, please modify the SQL statement above.

-- Delete product metadata
DELETE pm
FROM wp_postmeta pm
JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.post_type = 'product';
-- Delete product reviews (if any)
DELETE c, ct
FROM wp_comments c
LEFT JOIN wp_commentmeta ct ON c.comment_ID = ct.comment_id
JOIN wp_posts p ON c.comment_post_ID = p.ID
WHERE p.post_type = 'product';
-- Delete product category relationships
DELETE tr
FROM wp_term_relationships tr
JOIN wp_posts p ON tr.object_id = p.ID
WHERE p.post_type = 'product';
-- Delete product
DELETE FROM wp_posts
WHERE post_type = 'product';