As deleting is a very sensitive task, i’m not going to state a single query here that can not apply to your situation and result in potential data loss, but will instead try to guide you through the process of finding the products to be deleted.

Going through all of this process is only worth if you have multiple thousands of products to delete. You may be able to do things from the admin UI much easier for a 100 products in one category by just increasing the amount of products per page maybe, and using the bulk delte option.

If you still want to go through the database for this operation, you’d need to get products from the category you want to delete as stated in the article here. This would give you root level products ids to remove.

You can find variations by searching for product_variation post types (if you’re not using HPOS) for which parent_id is one of the root level product ids you’ve identified before.

Now you have the products and the variations to delete. The delete operation is usually best done in “cascade”. You can remove things from different tables using the IDs you collected.

One suggested order would be to go through: wp_postmeta (to remove related product data), wp_term_relationships (to remove the relations to attributes), wp_posts (to remove the actual product and variations).

Have in mind, that after removing a large amount of products, and depending on your stack, you may need to flush the object cache, and also there may be other data to be refreshed (like WooCommerce lookup tables for example).

I hope i’ve given you a rundown on the available options to perform this operation.

There’s many reasons why your orders list can be slow. Too many orders on the legacy data store may be one of the reasons why. Switching to HPOS may be a step in the right direction.

There may be a myriad of other things that can affect your order screen load times. Plugins that get information from the order and display it in the order table, plugins that change the default queries, autoloaded options, integrations that fetch live data for each individual order (bad practice, but it can happen), etc.

This would be an accurate statement here. As with most niche functionality in the WooCommerce ecosystem, there’s not a lot of offer, or if there is, it doesn’t entirely adhere to coding standards or performance best practices.

Even tho it may not be as extensive as other options mentioned here, or as WC Vendors which you mention, the one that sustained better at scale in our experience was WooCommerce Product Vendors, which is why it’s at the top of our list here.

I’ll get the list in the article revised so we can also mention WC Vendors, should it make sense. Thanks for bringing it up.

I would assume you’re asking about putting product and product related tables into different tables, which would be a common use case. In that case, WooCommerce included CRUD classes like 6 years ago, which can be used to define custom storage options, different than the regular posts and postmeta tables currently being used. As opposed to HPOS, there’s no official implementation of this, other than this outdated/unmaintained project here.

If you were to move WooCommerce products to separate database indeed, you could also rely on the CRUD classes and implement a datastore that keeps a separate database connection for these tables.

Hey Mark, this article should help answer that question!

Hi Jules,
Yes it would. As rules vary state to state in the US, THC products would require a different processor.