[Bug]: SQL Timeout on sitemap_index.xml for WordPress VIP Scale having 7M+ users

[josevarghese]

Prerequisites

• I've read and understood the contribution guidelines.
• I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened

Severe performance issue with the Author Sitemap Provider, causing 504 timeouts in their WordPress VIP environment due to inefficient database queries with 7.3M users.

The query generated in class-author-sitemap-provider.php (lines 207-215) takes 63.5 seconds to execute, exceeding the database threshold and breaking the sitemap index.

HS Ticket for more details: https://secure.helpscout.net/conversation/3276873734/1379595

Step-by-step reproduction instructions

Slow Query Log (WP VIP):

/sitemap_index.xml | Queries: 413 | Total DB time: 69.1757s

--- #1 [63.5859s] *** SLOW *** ---
SELECT wp_users.ID
FROM wp_users 
	LEFT JOIN wp_usermeta ON 
( 
wp_users.ID = wp_usermeta.user_id 
AND 
wp_usermeta.meta_key = '_yoast_wpseo_profile_updated' 
) 
LEFT JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )
WHERE 1=1 AND ( 
  ( 
    ( 
      wp_usermeta.user_id IS NULL
    ) 
    AND 
    ( 
      ( 
        ( 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"edit\\_posts\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"administrator\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"editor\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"author\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"contributor\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"wpseo\\_manager\"%' ) 
          OR 
          ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%\"wpseo\\_editor\"%' )
        )
      )
    )
  )
)	ORDER BY user_login ASC


  Origin:
    require('wp-blog-header.php')
    wp
    WP->main
    WP->query_posts
    WP_Query->query
    WP_Query->get_posts
    do_action_ref_array('pre_get_posts')
    WP_Hook->do_action
    WP_Hook->apply_filters
    WPSEO_Sitemaps->redirect
    WPSEO_Sitemaps->build_sitemap
    WPSEO_Sitemaps->build_root_map
    WPSEO_Author_Sitemap_Provider->get_index_links
    WPSEO_Author_Sitemap_Provider->update_user_meta
    get_users
    WP_User_Query->__construct
    WP_User_Query->query

Expected results

Actual results

1. The sitemap to load without any performance issues

Screenshots, screen recording, code snippet

https://docs.google.com/document/d/1iQL_g6MOixcsH8fvJBvJBlFS7_YwxrWFM6oB3JB_ZUc/edit?tab=t.0

Which editor is affected (or editors)

• Block Editor
• Gutenberg Editor
• Elementor Editor
• Classic Editor
• Other (please specify in additional info)

Which browser is affected (or browsers)

• Chrome
• Firefox
• Safari
• Other (please specify in additional info)

Device you are using

No response

Operating system

No response

PHP version

No response

WordPress version

6.9.3

WordPress Theme

No response

Yoast SEO version

27.3

Gutenberg plugin version (if relevant)

No response

Elementor plugin version (if relevant)

No response

Classic Editor plugin version (if relevant)

No response

Relevant plugins in case of a bug

No response

[josevarghese]

Note

Internal Slack conversation: https://yoast.slack.com/archives/C01NCRHHN30/p1775549537622379
HS: https://secure.helpscout.net/conversation/3276873734/1379595?viewId=799205

[josevarghese]

Please inform the customer of conversation # 1379595 when this conversation has been closed.