get_terms() retrieving 90K rows, generating performance issues

user_279591 0
Viewed 219

Recently I have have received under my responsibility the company's wordpress site, suffers from extreme performance issues...

I have started investigating, installed Query Monitor and discovered several problemas:

Get_terms().

The site's theme use heavily this method in many shortcodes. All this shortcodes call get_terms and at the end, it sums up to 88K queries that slows the site dramatically.

If I'll replace the theme, or disable Polylang, the memory will shrink down from 260mb to 20mb-30mb.

I'm Adding a print of the site's query monitor. (removing the theme's name to protect its identity)

Query monitor print screen

The slow query is this

SELECT t.*, tt.* 
  FROM wp_terms AS t
 INNER JOIN wp_term_taxonomy AS tt
             ON t.term_id = tt.term_id
 WHERE tt.taxonomy IN ('post_translations')
  AND tt.count > 0 
ORDER BY t.name ASC

I have managed several themes before. Never seen a theme that using so heavily get_terms().

Listen, I'm not a PHP guy. I'm a front man who does themes and enjoy it, but I think that in this case there is some problem of the way the method is called and I cant find out where.

As we can see in the print above, the plugin calls extend_shortcodes which calls get_terms() inside taxonomy.php. I was trying to find the expensive method call, and cannot seem to locate it.

Did anyone encountered a problem like that before? Does anyone has some tips to share? My site gets stuck too many times cause of this problem and I will apreciaste any help I can get.

wordpress-theming wordpress query-optimization mysql php
2 Answers

A 90K row resultset from that very common WordPress query of yours is, to put it bluntly, ludicrously, insanely, over-the-top, large. That term / taxonomy subsystem is mostly used to organize categories and tags. But, your theme, and possibly your Polylang translation system, uses it somehow to manage various languages.

I did an EXPLAIN ANALYZE on a test site and found that the MySQL (in my case MariaDB 10.6.5) query planner, for a very common value of wp_term_taxonomy.taxonomy, chooses to ignore the indexes on that table and scan the whole thing. For a less common value it uses the indexes, which is want we want. I guess if you run an EXPLAIN as suggested in the comments, you'll get something similar.

I know my remarks above may as well be written in ancient Klingon. So read on.

Indexing your table is not the way to solve your problem. The problem is a bloated wp_term_taxonomy table, and / or your theme code to retrieve all that bloat inappropriately. (What can a theme possibly do with a 90K-row result set???)

How to address the problem?

  1. install a plugin like Younes JFR's Advanced Database Cleaner. Take a look at the wp_term_taxonomy table. Does it offer you any chances to clean things up? If so, use it. (Back up your database first, of course.)

  2. Ask the Polylang support people for help. You can say "90K 'post_translations' rows? Really? WTF?" There may be some way in Polylang to clean this up. And, if in fact Polylang uses the term taxonomy system to store translated posts, well, WTF? Show them your Query Monitor output.

  3. Ask the people responsible for your theme why they need all that data to expand their shortcodes. (I assume it's a custom theme.) Show them your Query Monitor output too.

I know this is not a solution to your problem. But it should get you further down the troubleshooting path.

user_2056080

Add this "composite" index:

ALTER TABLE wp_term_taxonomy ADD INDEX(taxonomy, count);

I would use the mysql commandline tool; phpmyadmin or workbench could be used instead.

user_17668310
Related