SQL query to delete entire WooCommerce products in one hit

If you have a sizeable product list in WooCommerce of say min. 300 products but for some reason you have to delete the entire list, unfortunately WordPress is very limited and slow when it comes to deleting the data. Typically, deleting anything beyond 500 products may show a 503 or 504 gateway error.

Imagine if you had 7,000 products to delete; you would probably go bald before you could even finish the job!

But there’s another way and it’ll take seconds to complete the execution.

Take a backup of your database

Firstly, before we execute any SQL query, it is recommended that you take a backup of your database.

We recommend that you take a backup from the Staq interface. You can run a Manual Backup by following thes instructions.

Once you’ve completed a manual backup, please follow the steps below.

Install WP phpMyAdmin

The next step is to install a plugin inside your WordPress website.

We recommend that you install WP phpMyAdmin by following these instructions.

SQL Query

Once you’ve installed WP phpMyAdmin, click the following:

• go to the *_posts table on the left hand side. In the example below, it’s rfw_posts,
• followed by clicking SQL:

Then, delete the prefilled information and copy and paste this code (NOTE: if your database prefix is not the default wp_, you will need to replace this with the database prefix you are using):

DELETE relations.*, taxes.*, terms.*
FROM wp_term_relationships AS relations
INNER JOIN wp_term_taxonomy AS taxes
ON relations.term_taxonomy_id=taxes.term_taxonomy_id
INNER JOIN wp_terms AS terms
ON taxes.term_id=terms.term_id
WHERE object_id IN (SELECT ID FROM wp_posts WHERE post_type='product');

DELETE FROM wp_postmeta WHERE post_id IN (SELECT ID FROM wp_posts WHERE post_type = 'product');
DELETE FROM wp_posts WHERE post_type = 'product';

Then, click GO. If successful, you should see something like this:

Go back to the WordPress backend and click Products and you should see that the product lists should be empty. Voilà!