The wp_postmeta table is over 1 gig, what should I do?

Question

WordPress

Guys, probably the table wp_postmetain Wordpress is a real headache!
I have 214677 entries in it and it weighs 1.5 GiB (I use the InnoDB engine and utf8mb4_unicode_520_ci encoding). At the same time, there are wp_woocommerce_order_itemmeta 310731 records in the table and it weighs 34.6 MB .

Tried to clean manually, removed all `meta_value` = '', but this is a temporary solution. I also deleted all entries for non-existent posts (compared the presence of ids in the wp_posts and wp_postmeta tables, there is no post - we delete meta data as well) - it turned out to be only 78 posts.

All in all, not much has been removed. Perhaps there are some plugins or smarter SQL queries?

On average, the site loads in 0.801934 seconds with 33 database queries (first load) and 0.592067 seconds with 27 database queries (repeated). The site has over 2,000 products and 18,000 WooCommerce orders.

This is how the tables look now:

Therefore, the following questions arose:

1. Ignore the situation or look for a way to clean the database from garbage?
   
2. Why does the wp_postmeta table weigh more than tables with the same number of rows?
   
3. Can the InnoDB engine and utf8mb4_unicode_520_ci encoding somehow affect the database size? Should I change them or leave them as they are?
   

Answer 1

Last night, just in the telegram channel, a question was asked :)

SELECT meta_key, meta_value, length(meta_value) 
FROM wp_postmeta 
ORDER BY length(meta_value) DESC
LIMIT 10;

- this query will show you the 10 heaviest records in this table
- by changing LIMIT you can see more/less records
- length(meta_value) will show the weight of the value in bytes
As a result, it allowed us to find some unnecessary heavy records in the table even after cleaning with 1+ Giga decreased to 300MB.

Answer 2

Try sorting the table by row weight and see which rows are the heaviest and what is stored in them.

Answer 3

You can also look at some plugin ala https://uk.wordpress.org/plugins/advanced-database...
As advised: check what metadata you have stored. Perhaps some plugin or something else creates a bunch of entries for each post.
In general, I would ignore the situation. You do not have all the metadata loaded at a time. But I'm not a database expert.