sitemap slow query

Hello,

I noticed that Yoast SEO Plugin has been choking up a few databases on a few instances I manage. It looks like a query revolving around author sitemaps? Below is an example of the query, this hangs the database and it sits at 100-199% CPU. Obviously the _usermeta tables on these databases are massive. I’m assuming this is query is ran by wp-cron? or? Is there anyway to disable this?

+------+---------+-------------------------------+----------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id   | User    | Host                          | db       | Command | Time | State     | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+------+---------+-------------------------------+----------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 8074 | ebdb | 358sdj-ec2-rds.aws.amazon.com:3306 | wpdb | Query   |  1690 | preparing | SELECT DISTINCT wp_users.ID FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id )  LEFT JOIN wp_usermeta AS mt3 ON (wp_users.ID = mt3.user_id AND mt3.meta_key = 'wpseo_excludeauthorsitemap' )  LEFT JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) WHERE 1=1 AND wp_users.ID IN ( SELECT DISTINCT wp_1009_posts.post_author FROM wp_1009_posts WHERE wp_1009_posts.post_status = 'publish' AND wp_1009_posts.post_type IN ( 'post', 'page', 'attachment', 'project', 'idx-wrapper', 'idx_page' ) ) AND (
  (
    (
      wp_usermeta.meta_key = '_yoast_wpseo_profile_updated'
      AND
      (
        ( mt1.meta_key = 'wp_1009_user_level' AND mt1.meta_value != '0' )
        AND
        (
          ( mt2.meta_key = 'wpseo_excludeauthorsitemap' AND mt2.meta_value != 'on' )
          OR
          mt3.user_id IS NULL
        )
      )
    )
    AND
    (
      mt4.meta_key = 'wp_1009_capabilities'
    )
  )
) ORDER BY wp_usermeta.meta_value+0 DESC |
| 8158 | ebdb | 358sdj-ec2-rds.aws.amazon.com:3306 | wpdb | Query   |  1660 | preparing | SELECT DISTINCT wp_users.ID FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id )  LEFT JOIN wp_usermeta AS mt3 ON (wp_users.ID = mt3.user_id AND mt3.meta_key = 'wpseo_excludeauthorsitemap' )  LEFT JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) WHERE 1=1 AND wp_users.ID IN ( SELECT DISTINCT wp_1009_posts.post_author FROM wp_1009_posts WHERE wp_1009_posts.post_status = 'publish' AND wp_1009_posts.post_type IN ( 'post', 'page', 'attachment', 'project', 'idx-wrapper', 'idx_page' ) ) AND (
  (
    (
      wp_usermeta.meta_key = '_yoast_wpseo_profile_updated'
      AND
      (
        ( mt1.meta_key = 'wp_1009_user_level' AND mt1.meta_value != '0' )
        AND
        (
          ( mt2.meta_key = 'wpseo_excludeauthorsitemap' AND mt2.meta_value != 'on' )
          OR
          mt3.user_id IS NULL
        )
      )
    )
    AND
    (
      mt4.meta_key = 'wp_1009_capabilities'
    )
  )
) ORDER BY wp_usermeta.meta_value+0 DESC |
| 8218 | ebdb | 358sdj-ec2-rds.aws.amazon.com:3306 | wpdb | Query   |  1600 | preparing | SELECT DISTINCT wp_users.ID FROM wp_users LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )  LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )  LEFT JOIN wp_usermeta AS mt2 ON ( wp_users.ID = mt2.user_id )  LEFT JOIN wp_usermeta AS mt3 ON (wp_users.ID = mt3.user_id AND mt3.meta_key = 'wpseo_excludeauthorsitemap' )  LEFT JOIN wp_usermeta AS mt4 ON ( wp_users.ID = mt4.user_id ) WHERE 1=1 AND wp_users.ID IN ( SELECT DISTINCT wp_1009_posts.post_author FROM wp_1009_posts WHERE wp_1009_posts.post_status = 'publish' AND wp_1009_posts.post_type IN ( 'post', 'page', 'attachment', 'project', 'idx-wrapper', 'idx_page' ) ) AND (
  (
    (
      wp_usermeta.meta_key = '_yoast_wpseo_profile_updated'
      AND
      (
        ( mt1.meta_key = 'wp_1009_user_level' AND mt1.meta_value != '0' )
        AND
        (
          ( mt2.meta_key = 'wpseo_excludeauthorsitemap' AND mt2.meta_value != 'on' )
          OR
          mt3.user_id IS NULL
        )
      )
    )
    AND
    (
      mt4.meta_key = 'wp_1009_capabilities'
    )
  )
) ORDER BY wp_usermeta.meta_value+0 DESC |
| 8349 | ebdb    | localhost                     | NULL     | Query   |    0 | NULL      | SHOW FULL PROCESSLIST                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| 8353 | ebdb | 358sdj-ec2-rds.aws.amazon.com:3306 | wpdb | Sleep   |    0 |           | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+------+---------+-------------------------------+----------+---------+------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

• This topic was modified 8 years, 1 month ago by chaos.