Unexpected very slow user count in WordPress

Question

I have a Wordpress page that returns timeout error every so often. When reviewing the MySQL "slow queries" log, I could notice that there is an extremely slow query coming from Wordpress, but I don't know exactly which module:

SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  wpe_users
    WHERE  1=1
      AND  wpe_users.ID IN (
        SELECT  DISTINCT wpe_posts.post_author
            FROM  wpe_posts
            WHERE  wpe_posts.post_status = 'publish'
              AND  wpe_posts.post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' ) 
                          )
    ORDER BY  display_name ASC
    LIMIT  0, 10;

The query appears two or more times, with State Copying to tmp table and Time of up to 53815.44 (secs? 15 hours? it's crazy!). And it makes the Mysql process consume 557% of CPU and 8.7% of RAM

The wpe_users table contains almost 3 million entries and the wpe_posts table contains 68,000 entries. The wpe_comments table is also starting to cause problems as it contains 361,000 entries.

I use pagination for comments and posts but not for users. The question is, how can I fix the problem without disabling paging? How can I identify where the user query is coming from if I don't show users anywhere?

What have I tried?

• I updated Wordpress to the latest version available.
• I tried to identify where these queries come from using the "Query Monitor" plugin but they don't appear.
• I verified the only 3 plugins that I have active and none of them generate such queries.
• I closed the administration panel and killed said processes to see if the queries were generated by the administration panel but they start again...

I don't have much experience with MySQL configuration. I am using MySQL 5.5.62 with Plesk panel on a VPS 5GB RAM 6 Cores

SHOW CREATE TABLE wpe_users

CREATE TABLE `wpe_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`),
  KEY `user_email` (`user_email`)
) ENGINE=InnoDB AUTO_INCREMENT=3492068 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN wpe_users query

+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
| id | select_type        | table     | type  | possible_keys                | key              | key_len | ref  | rows    | Extra                        |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
|  1 | PRIMARY            | wpe_users | ALL   | NULL                         | NULL             | NULL    | NULL | 2780982 | Using where; Using filesort  |
|  2 | DEPENDENT SUBQUERY | wpe_posts | range | type_status_date,post_author | type_status_date | 164     | NULL |    2265 | Using where; Using temporary |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+

Answer 1

Add this composite index to wpe_posts: INDEX(post_status, post_type, post_author).

The answer to "why": In 5.5 (and before), IN ( SELECT ... ) was a terribly slow construct.

Amd see if this runs faster:

SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  ( SELECT  DISTINCT post_author
            FROM  wpe_posts
            WHERE  post_status = 'publish'
              AND  post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' ) 
          ) AS x
    JOIN wpe_users USING wpe_users.ID = x.post_author
    ORDER BY  display_name ASC
    LIMIT  0, 10;

For further discussion, please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.

Be aware that a future version is likely to remove the SQL_CALC_FOUND_ROWS feature, forcing you to make two SELECTs.