Slow Wordpress Core SQL Query since website migration and upgrade to MariaDB 10.7.3

Question

Since a website migration from debian 9 and mariadb 10.5 to debian 11 and mariadb 10.7.3, I struggle with slow queries, especially one that comes from Wordpress Core.

We identified it with my hosting manager, but even the plugin "Query Monitor" shows it at the "slow one".

Here's the query : SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (77703) ORDER BY t.name ASC

Thank you in advance for your help.

P.S : We are trying to change the db version back to 10.5 cause my hosting manager says it maybe comes from optimize compatibility issues with the new mariaDB version. But I can't stay on 10.5 forever...

Answer 1

Yes, the Core does a poor job in indexing tables. For that query, I recommended these indexes:

tr:  INDEX(object_id, term_taxonomy_id)
t:  INDEX(term_id, name)
tt:  INDEX(taxonomy, term_id,  term_taxonomy_id)

This plugin speeds up a lot of queries, possibly yours: https://wordpress.org/plugins/index-wp-mysql-for-speed/