1

For the past two days in a row, my host has taken my site offline because it uses too many resources.

In both cases, I received a report detailing variations on the same MYSQL query:

USER: xxx_wrdp1

DB: xxx_wrdp1

STATE: Creating sort index

TIME: 1

COMMAND: Query

INFO: SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_status, post_modified_gmt, post_date, post_date_gmt

FROM ( SELECT wp_posts.ID

FROM wp_posts

WHERE wp_posts.post_status IN ('publish') AND

wp_posts.post_type = 'post'

AND wp_posts.post_password = ''

AND wp_posts.post_date != '0000-00-00 00:00:00'

ORDER BY wp_posts.post_modified ASC LIMIT 100 OFFSET 142200

)

o JOIN wp_posts l ON l.ID = o.ID

This is not a query I've run manually, so I'm trying to understand if it's being run by a plugin or some other automated process so I can either improve or stop it.

If you have any suggestions on how I can find out what's prompting the query to run, I'd appreciate it. Thanks!

user2258740
  • 199
  • 2
  • 11
  • some suggestions: use EXPLAIN to see the execution plan for the query. Likely the inline view is cranking a "Using filesort" (to satisfy the ORDER BY) on a huge number of rows. The naive `LIMIT n OFFSET o` approach to implementing paging works for small sets, but it's hugely inefficient on larger sets. – spencer7593 Jun 27 '19 at 16:15
  • @spencer7593 I appreciate the context, but--if I've understood this--you're making recommendations on how to improve the query. But, I'm trying to find out why the query is running at all. If I've misunderstood, let me know. – user2258740 Jun 27 '19 at 16:30
  • Looks to me like there was a request to get a list of published posts (status = 'publish'), starting with the posts with the oldest modified date up through the newest modified date. It looks like the list is broken up into pages of 100 posts per page, and this particular request was for page 1423. I suspect that the web server access log would show a request corresponding to this query; we could correlate by time. I suspect that there was a request for page 1422 before this one, and a request for page 1421 before that, ... – spencer7593 Jun 27 '19 at 17:54

0 Answers0