Deleting all WooCommerce products quickly using SQL statements

January 30, 2024

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, hook-firing 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 only the process-deletes stage, force-deletes the posts (skipping trash for speed), and goes through the normal WordPress delete path so hooks fire and dependent data (search index, filters, caches) stays consistent. 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). This is precisely the consistency you get for free when you use wp ssi <id> --delete-all instead.

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 cleanest option remains:

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

which removes exactly the posts that import created, the right way, with hooks firing so every dependent system stays in sync.

×
▲
▼
1/1