2

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

Server Error and Mysql Process List

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 |
+----+--------------------+-----------+-------+------------------------------+------------------+---------+------+---------+------------------------------+
5
  • Not sure how much help this will be, but that query is sent to the database on line 2927 of /wp-includes/class-wp-query.php. It's built right into the get_posts() function which is arguably the most important block of code in the entire package ...
    – matigo
    Commented May 20, 2021 at 5:16
  • 5.5.62 is a very old version of MySQL (very unsupported). Why has this only shown up now? You could take your backup and restore to a newer MySQL or MariaDB version and see if it gets better performance on the same query. Use EXPLAIN {query} to see how its going to execute and compare this to your existing server.
    – danblack
    Commented May 20, 2021 at 5:41
  • @danblack Thank you, I will do that. This problem is not new, I have had it for more than two years, and it was the reason why I left the project. Now I'm going back to it and I want to solve this problem that brought me so many headaches, wordpress gets saturated and crashes all the time.
    – Joseph
    Commented May 20, 2021 at 5:56
  • 557% of CPU, that's a lot :) Commented May 21, 2021 at 6:41
  • The offending query shows the first ten authors by name. With three million users, that seems pointless. Does your theme have such a leaderboard anyplace? @danblack correctly pointed out that mysql v 5.5 is more than a decade old, and reached end-of-life in 2018. Tell your hosting provider to upgrade, or dump them for a better one. Seriously. Later versions have many performance improvements, including for the query pattern ID IN (SELECT...). And try tidying up your database with a plugin like Advanced Database Cleaner.
    – O. Jones
    Commented May 21, 2021 at 10:49

1 Answer 1

2

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.

3
  • This query is made by Wordpress and I am not sure what its function is, I would like to avoid it but I cannot identify what generates it ...
    – Joseph
    Commented May 21, 2021 at 4:19
  • I gotta chime in. (1) 3 megausers is really a lot for the way WordPress is designed. (2) For a site with this many users, your chance of success is quite low unless you upgrade to MySQL 8+ or MariaDB 10.3+, (3) Rick James and I have created a WordPress plugin to give the tables better indexes. wordpress.org/plugins/index-wp-mysql-for-speed . It indexes wpe_users.display_name which might help this ORDER BY ... LIMIT antipattern. But SQL_CALC_FOUND_ROWS is still going to force a table scan. It may be time for another plugin that rewrites bad queries. Whatta mess. Sigh.
    – O. Jones
    Commented Feb 17, 2022 at 12:29
  • SQL_CALC_FOUND_ROWS is deprecated. So, rethink the need for the total row count.
    – Rick James
    Commented Feb 17, 2022 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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