Issue with WooCommerce too big database size especially post_meta

Question

I have a Woocommerce site with around 10K products but got a 5.1 GiB database size and post_meta only occupy 4.5 GiB but the wp_posts table is 350 MB only.

I have tried the following query but still not helping:

**Deleting orphaned Post Meta in WordPress**

SELECT * FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID IS NULL

OR 

SELECT * FROM wp_postmeta pm
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id
WHERE wp.ID NOT IN (SELECT ID FROM wp_posts)

**Identify attachment metadata whose posts were deleted**

SELECT * 
FROM wp_posts 
WHERE 
  -- the attachments
  post_type = 'attachment' 
  AND (
    -- ignore anything without a parent
    post_parent != 0 
    -- filter on anything that has a parent that does not exist
    AND post_parent NOT IN (SELECT ID FROM wp_posts)
  )

Still not helping, please let me know how I can optimize the wp_postmeta table?

I have lots of attributes so this _product_attributes serialized data is too big for each product. If we already got terms and taxonomy table why do they need to save again on _product_attributes post_meta. :(

Answer 1

post_meta (in WordPress or WooCommerce) is inefficiently indexed. Suggest you add this plugin: WP Index Improvements

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

(Deleting unused data from any big table rarely helps as much as improving the indexes.)