How to solve slow WordPress site caused by attachment_metadata

My WordPress sites starting to become slower by time, after two years they become very slow. I tried many things, such as:

• improved my DB,
• used cache plugins (WP Rocket and W3 Total Cache) which both are
  great, rocket is a little better but you have to pay for it,
• checked effects of analytics on my site (deactivating slimstat
  analytics improved my response time by 20%),
• handling heart beat (WP Rocket has a way to control heart beat).

Finally I checked core tables of WordPress (wp_posts and wp_postmeta) the main reason that my WordPress site is slow is because of the wp_postmeta, and unfortunately WooCommerce save its meta in the wp_postmeta which affects response time drastically.

All of these aside, there is one more thing that has strong effect on the speed, and that is the length of _wp_attachment_metadata which for my site is more than 1024 characters.

How could I optimize this (_wp_attachment_metadata) metadata without losing all the image-sizes that I need?

Finally, if I change the type of meta_value of wp_postmeta from longtext to varchar(2024) (which I assume all plugins produce meta data with less characters) and then index it. Would it make any problem with core of WordPress?

Rick James and I have created the (free, GPL) Index WP MySQL for Speed plugin. It addresses some of the performance problems with wp_postmeta by adding database indexes suitable for that table’s use cases. I use it myself on a Woo site with several thousand products. (It works on users, terms, options, and comments too.)

If you’d prefer not to use a plugin, you can do the same work with phpmyadmin or some similar tool. Here’s the SQL Data Definition Language the plugin uses to adjust wp_postmeta.

Before you do this make sure…

• you’re using MySQL InnoDB tables
• MySQL/MariaDB 5.7+
• you have a backup

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

These adjusted / added database keys match the common query patterns from WP core and Woo.