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
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