Useful SQL Scripts

Deleting all WooCommerce products quickly using SQL statements

Published · Last updated

If you are testing Super Speedy Imports, or for any other reason, sometimes you just want to delete all your products quickly.

If you have millions of products, deleting them through the wp-admin interface will take days. Deleting them through direct SQL commands takes minutes.

Prefer the SSI-native alternatives first

Raw SQL is fast but blunt. Before reaching for it, consider the maintained alternatives that Super Speedy Imports ships with:

  • wp ssi <id> --delete-all — deletes every post/product/CPT created by a given import id. This runs the process-deletes stage, which moves the posts to the trash, or deletes them permanently when the import has Force delete (skip trash) ticked. It works in set-based SQL, not through wp_delete_post(), so it fires no WordPress or WooCommerce deletion hooks (wp_trash_post, before_delete_post, deleted_post, woocommerce_delete_product and the rest). Unlike the raw SQL below, it touches only that import’s items, rebuilds the Super Speedy Search and Super Speedy Filters indexes afterwards whenever those plugins are active, and keeps WooCommerce’s lookup tables in step itself: trashing removes the items’ attribute lookup rows, and a permanent delete also removes their metadata, term assignments and product meta lookup rows. It does not delete terms or images. See the CLI reference for full syntax.
  • Wiping WordPress for a fresh import — broader strategies (truncating posts/postmeta/terms, resetting auto-increment, dealing with media) when you want a genuinely clean slate rather than just removing one import’s products.

Use the raw SQL below only when you specifically want the speed of direct DELETE statements and you understand the trade-offs.

Requirements and things to know

You must be able to run SQL commands against your database. This can be through phpMyAdmin, through SSH (wp db query / the mysql client), or the SQL Executioner plugin.

Take a database backup before running this. These statements are irreversible.

These statements bypass WordPress entirely, so no do_action() hooks fire. That means anything that listens for product deletion — Super Speedy Search’s index, Super Speedy Filters’ ancestor cache, WooCommerce’s own lookup tables, third-party plugins, object caches — will not be updated automatically. If you run the raw SQL, you are responsible for rebuilding those yourself (e.g. re-run the relevant plugin’s re-index tool). wp ssi <id> --delete-all keeps WooCommerce’s lookup tables in step and rebuilds the Super Speedy Search and Super Speedy Filters indexes when those plugins are active, but it fires no deletion hooks either, so any other plugin’s index needs the same rebuild after it.

Replace the wp_ prefix in every statement below with your site’s actual table prefix if it differs.

SQL statements to delete all your products quickly

To do this properly we delete from the leaf tables first and the wp_posts table last, so that while we run each statement we can still resolve which rows belong to a product or product_variation.

Delete term meta for product-attached terms:

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 the terms themselves:

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 the term taxonomy rows:

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 the term relationships:

DELETE FROM wp_term_relationships
WHERE object_id IN (
    SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);

Delete the product post meta:

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

Delete the post meta of attached images (attachments whose parent is a product):

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 the attachment posts themselves:

DELETE FROM wp_posts
WHERE post_type = 'attachment'
AND post_parent IN (
    SELECT ID FROM wp_posts WHERE post_type IN ('product', 'product_variation')
);

Finally, delete the products and variations:

DELETE FROM wp_posts WHERE post_type IN ('product', 'product_variation');

Note: attachment rows are only deleted from the database. The image files on disk are not removed by these statements — clean those up separately if you need the disk space back.

Rebuilding plugin data afterwards

Because the raw SQL fires no hooks, any plugin that keeps a derived copy of your product data will be left out of date and must be rebuilt:

  • Super Speedy Search maintains its own search index. After a raw-SQL wipe, re-run its indexing tool so the index no longer references deleted products.
  • Super Speedy Filters maintains an object-ancestors cache. Rebuild it from the plugin’s settings after deleting products.
  • WooCommerce maintains product lookup tables (e.g. wp_wc_product_meta_lookup). Use WooCommerce’s “Regenerate” tools under WooCommerce → Status → Tools if your store still references stale products.

(These are separate plugins with their own tables and tools — they are not part of Super Speedy Imports, and SSI does not manage their data. The earlier version of this guide hard-coded DELETE statements against wp_superspeedysearch and wp_fww_object_ancestors; those are omitted here because their schemas are owned by those plugins and are better cleared through each plugin’s own re-index/rebuild tool than by raw SQL.)

If you imported the products with Super Speedy Imports in the first place, the cleaner option is:

wp ssi <import-id> --delete-all

which removes exactly the posts that import loaded, keeps WooCommerce’s lookup tables in step, and then rebuilds the Super Speedy Search and Super Speedy Filters indexes when those plugins are active, whatever the import’s After Import settings say. It fires no deletion hooks, so rebuild any other plugin’s index that holds product data afterwards.

Written for Super Speedy Imports 3.74.4.

Fixing this on a real store?

Super Speedy Imports does it for you. Or get every Super Speedy plugin with the All Access Pass.

Super Speedy Imports

Big supplier feeds imported and updated in minutes, not overnight

From €79.00
Get Super Speedy Imports
All Access Pass

Every Super Speedy plugin, one licence, with updates and support.

From €129.00
See the All Access Pass

Comments