Extremely slow query on Users screen

Hi,

I’m experiencing a severe performance problem caused by a query generated by Ultimate Member. It takes 60–67 seconds to run and examines over 33 million rows in wp_usermeta, even though my database is small and clean.

Environment / data context:

• About 8,000 users
• wp_usermeta has only ~400,000 rows
• 0 orphaned usermeta rows
• The standard indexes on user_id and meta_key are present and used
• Site also runs WooCommerce (HPOS enabled). Many users are WooCommerce customers with an empty account_status.

The problem seems structural rather than data-related: the query uses SELECT DISTINCT SQL_CALC_FOUND_ROWS and multiple LEFT JOINs on the same wp_usermeta table where some joins (mt1, mt3) have no meta_key condition in the JOIN clause. This produces a combinatorial explosion (≈50 meta × 50 meta per user × thousands of users), which is what drives the 33M examined rows.

Here are two entries from my slow query log:

# Query_time: 60.907194  Rows_sent: 1  Rows_examined: 33880686
SELECT DISTINCT SQL_CALC_FOUND_ROWS wp_users.ID
FROM wp_users
LEFT JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id AND wp_usermeta.meta_key = '_um_registration_in_progress' )
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 AND mt2.meta_key = 'account_status' )
LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id )
WHERE 1=1 AND (
  ( wp_usermeta.user_id IS NULL OR ( mt1.meta_key = '_um_registration_in_progress' AND mt1.meta_value != '1' ) )
  AND
  ( mt2.user_id IS NULL OR ( mt3.meta_key = 'account_status' AND mt3.meta_value = '' ) )
)
ORDER BY user_login ASC
LIMIT 0, 50;

# Query_time: 67.558859  Rows_sent: 0  Rows_examined: 33886516
(identical query)

Questions:

1. What page/function in UM triggers this query? It appears related to counting/listing users by account status.
2. Is there a way to make the mt1 and mt3 joins include the meta_key in the JOIN condition (or otherwise avoid the unindexed self-joins) so MySQL doesn’t explode the row count?
3. Is SELECT DISTINCT SQL_CALC_FOUND_ROWS still required here, given it’s a known performance bottleneck?
4. Is there a recommended setting or workaround (e.g. user cache) to prevent this query from running on admin pages?

I’m running the latest version of Ultimate Member. Any guidance would be appreciated. Thanks!