CODEWITHSUNDEEP
mysqlwordpresshosting
user2258740
user2258740

Reputation: 309

How can I reduce MYSQL usage when sorting this index for a Wordpress site?

I received a note from Hostgator indicating that they've restricted my access to MYSQL because my site is using too many resources.

Normally, in a case like this, I'd just restore a backup to see if I recent change was producing the error. But, I haven't made any recent changes--apart from writing a few new posts.

Here's the report Hostgator included with their note--with my personal details removed:

Running Queries:

*************************** 1. row ***************************

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](https://l.ID) = [o.ID](https://o.ID)

If any can offer a suggestion on what's causing this the uptick in resource usage (or how it can be remedied), I'd be curious.

Thanks!

Upvotes: 0

Views: 948

Answers (1)

Artur Luiz Oliveira
Artur Luiz Oliveira

Reputation: 107

Why that query has an inner select from the same table? Is there a specific purpose? (I extracted the query from report you posted)

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
) o
JOIN wp_posts l
  ON l.ID = o.ID

If that was the case, i'd change the query to:

SELECT
    ID,
    post_title,
    post_content,
    post_name,
    post_parent,
    post_author,
    post_status,
    post_modified_gmt,
    post_date,
    post_date_gmt
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

In this late example, you need to run the query with the EXPLAIN tag to check in which table/column it would require a proper index (usually when it says that rows count are bigger than 1).

Upvotes: 0

Related Questions