Critical Performance Issue wordpress multisite

WordPress 6.9 Multisite, subsite, Ultimate Member 2.11.1

Hello. First off, I want to express my appreciation for this excellent plugin. It has been a vital tool for our infrastructure, providing great functionality and flexibility. We heavily rely on it across our network.

However, we are running a large WordPress Multisite installation (1,000+ sites) sharing the global user tables, and we identified a specific bottleneck that I believe could help you improve the plugin for high-scale environments.

The Problem: The plugin executes a heavy SELECT DISTINCT SQL_CALC_FOUND_ROWS query joining wp_users with multiple instances of wp_usermeta to filter users by _um_registration_in_progress and account_status.

Since the standard WordPress database structure does not include a composite index for (meta_key, meta_value), MySQL performs a Full Table Scan on the usermeta table for every check. In a large network, this causes:

1. Massive I/O usage.
2. Query execution times exceeding 100+ seconds.
3. Table locking chains that paralyze the entire network.

The Query Identified:

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 )
...
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 = '' ) )
)
...