2

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?

6
  • 2
    How many rows are there in wp_posts and wp_postmeta? Have you done any profiling to check, which queries are slow? Jan 6, 2019 at 11:45
  • there are over 40000 posts and about half million postmeta, yes I checked all queries,(using wordpress.org/plugins/query-monitor - The query Monitor Plugin) and I optimized all possible queries. and the only thing that I can't do anything about is, _wp_attachment_metadata, but on a localhost (test environment) I did this query: "DELET FROM wp_postmeta where char_length(meta_value)>2024" and it deleted about 800 rows, yet improved my response time very effectively. That's why I'm looking for a way to reduce the size of meta_value of _wp_attachment_metadata Jan 6, 2019 at 20:39
  • Are these 40,000 unique posts, or do you have a lot of post revisions? If revisions are enabled, just disabling them or removing the current revisions will save a lot of space.
    – WebElaine
    Jan 7, 2019 at 14:14
  • These are unique posts Jan 7, 2019 at 19:40
  • 1
    @ArashRabiee have you find solution for this since then? If so, would you be kind to answer your own question here? I'm interested in your findings. Best wishes, W Jul 4, 2020 at 11:24

2 Answers 2

2

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.

2
  • 1
    For me, this worked great, as the plugins I'm using created a lot of meta content, that meant millions of rows being created. This made the job of querying the postmeta table a lot faster. I'd recommend that this gets included in core :) Oct 23, 2021 at 4:58
  • Thanks for the positive report. As for including the plugin's functionality in core, it uses some arcane features of MySql but only in the newer versions of the DBMS where they're available. Some big hosting providers (hi, Godaddy) still use the obsolete older versions. And core has to support everything.
    – O. Jones
    Oct 23, 2021 at 11:36
0

I do the following to improve a little bit better performance,

  1. limited revision to 3 by define('WP_POST_REVISIONS', 3); in wp_config.php
  2. moved to another host which supported Light Speed Cache, which has a little better performance than my previous caching solution
  3. updated woocommerce to version 4+
  4. Increased wordPress memory limit to 512 define(‘WP_MEMORY_LIMIT’, ‘256M’); in wp_config.php or asking your hosting provider
  5. removed analytics plugins from my site and use the option which my hosting provide for me
  6. Using a CDN for images and using webp format
  7. I used WPML for translating, so I cleaned the history from plugin setting, and afterward I removed all data from WP_PREFIX_icl_strings and only translated the required plugins, it has huge affect on my site; however, I had to check and re-translate most pages of my site (not post, only pages, archives etc)
  8. Using database clean up plugin, TAKE A BACK UP BEFORE USING THIS,
  9. Finally I used POD plugin and created some custom post_type which do not uses wordpress default tables, but this one is a little tricky and not recommended

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.