Quote from Tatiana on December 27, 2021, 12:17 pm

First of all, thank you very much for this plugin. I’ve been using it for 2.5 years and it’s great.

I have half a million posts, more than 10M views.

And I have two issues with mysql queries that work extremely slowly on a large site.

1. The first one wasn’t an issue until I moved my site to better and faster and more expensive server at Hetzner. And the site almost collapsed because the execution time for one mysql-query was 6-8 second.

The query was this one, it’s used in Recent Posts Widget:

SELECT p.id FROM wp__forum_posts AS p LEFT JOIN wp__forum_topics AS t ON (t.id = p.parent_id) WHERE p.forum_id IN(34,4,5,6,....) AND t.approved = 1 ORDER BY p.id DESC LIMIT 3;

The problem was that the query wasn’t suitable for newer software (MariaDB) but all servers will be upgraded to MarisDB within a year (which may cause problems with other sites that use your plugin).

If you want I can send you the full answer with EXPLAINS, here’s just the text from support about this particular query:

the difference between the execution speeds of your particular query seem to be caused by differences in the SQL query optimizer strategies used by MySQL and MariaBD.

New managed servers will be shipped with an updated software stack, which includes MariaDB 10.5 instead of MySQL 5.7.
The reason for this is, that MySQL is no longer shipped by our Linux distibution and upgrading to MySQL 8.0 has some additional drawbacks.

You can see the difference in the query optimization – MySQL first uses the wp__forum_posts table, the WHERE condition affecting the table plus the ORDER BY … DESC option in conjunction with the LIMIT statement.
This means MySQL _first_ does a ORDER BY on the column p.id, then LIMITs the results to 3 and only after this optimization applies the JOIN and the other statements.

MariaDBs query optimizer on the other hand decides to process the table wp__forum_topics first, which means that it does the JOIN first, then uses a temporary table and sorts the results, before applying the rest of the statements. This results in a considerably slower execution speed for this particular query.

When not using LIMIT, both database engines behave the same (and take the same amount of time), however, MySQL seems to chose the more clever strategy in the particular LIMIT case.

Relying on LIMIT is in general a bad idea, so you should avoid it, whenever possible, however, if you want to keep your query anyway, you could use the STRAIGHT_JOIN statement to force MariaDB to use a similar query.

Thus, replacing the LEFT JOIN with STRAIGHT_JOIN reduces the query execution speed on MariaDB to the level of MySQL.

You might ask, if it is possible to downgrade your new server to our old software platform – I am sorry to inform you that this is not possible.

All new servers are shipped with MariaDB (10.5) and all old managed servers will be upgraded to our new software platform within a year. So, eventually, your older server will also be upgraded to the new software stack including MariaDB 10.5.

2. The second issue can be found while getting the list of users in a Usergroup.

The query is this:

SELECT wp__users.ID,wp__users.display_name
FROM wp__users
INNER JOIN wp__usermeta
ON ( wp__users.ID = wp__usermeta.user_id )
WHERE 1=1
AND ( wp__usermeta.meta_key = 'wp__capabilities' )
AND wp__users.ID IN (4,1,15,13,5,10,11,...................)
ORDER BY ID ASC

It seems to list ALL users ids in IN ().

Since I have more than 100000 registered users, the query becomes just enormous, pages and pages long. And extremely slow as well.

—

I hope you read this post. Maybe it can help you in your further developments.