Pending Vendor tab: `pre_get_users` filter stacks meta_query groups → 141M-row scan on a 24k-row usermeta table

[maylard]

Environment

• Dokan Lite 5.0.14 (files verified against wordpress.org checksums — unmodified), Dokan Pro 5.0.13
• WordPress 6.x, WooCommerce, PHP 8.3, MySQL 8.0.46, Ubuntu 24.04
• Small marketplace: 319 users (317 sellers), wp_usermeta = 23,969 rows
• Reviewed the 5.0.15–5.0.17 changelogs and searched open/closed issues — this appears unreported and unfixed as of v5.0.17.

Summary

Loading wp-admin → Users → Pending Vendor (users.php?role=pending_vendor) produces a WP_User_Query whose SQL examined 141,238,050 rows to return a single value, running 193–221 seconds per request on a dedicated 4-core box and pinning MySQL at ~78% CPU. Three tab loads ≈ 10 minutes of saturated CPU. On this site the query returns 0 — there are no pending vendors at all (every seller has dokan_enable_selling = yes).

Root cause

Two code paths independently add dokan_enable_selling criteria to the same query:

1. WeDevs\Dokan\Vendor\Manager::get_vendors() builds a meta_query group for status => pending (dokan_enable_selling = 'no'). It is invoked with number => 1, fields => 'ID' by dokan_get_pending_vendor_count() (new @since 5.0.13, called from Admin\Dashboard\Pages\Vendors::menu() to draw the pending badge).
2. WeDevs\Dokan\Admin\UserList::filter_pending_vendors() hooks pre_get_users, which fires for every WP_User_Query on the page — including the badge query above. On users.php?role=pending_vendor it appends a second group: relation OR [ dokan_enable_selling = 'no', dokan_enable_selling NOT EXISTS ] plus role__in [seller, administrator].

Stacked, WordPress compiles four LEFT JOIN wp_usermeta — three with no meta_key constraint in the ON clause — so each of the ~312 users fans out into ~77 × 77 × 77 joined meta rows before the WHERE filters anything (confirmed with EXPLAIN: four ref joins on the user_id index, Using temporary).

Captured query (slow query log, long_query_time = 5)

# Query_time: 221.024085  Lock_time: 0.000003 Rows_sent: 1  Rows_examined: 141238050
SELECT DISTINCT SQL_CALC_FOUND_ROWS 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 AND mt2.meta_key = 'dokan_enable_selling' )
  LEFT JOIN wp_usermeta AS mt3 ON ( wp_users.ID = mt3.user_id )
 WHERE 1=1 AND (
   (
     ( wp_usermeta.meta_key = 'dokan_enable_selling' AND wp_usermeta.meta_value = 'no' )
     OR
     ( ( mt1.meta_key = 'dokan_enable_selling' AND mt1.meta_value = 'no' ) OR mt2.user_id IS NULL )
   )
   AND
   (
     ( mt3.meta_key = 'wp_capabilities' AND mt3.meta_value LIKE '%"seller"%' )
     OR
     ( mt3.meta_key = 'wp_capabilities' AND mt3.meta_value LIKE '%"administrator"%' )
   )
 )
 ORDER BY ID ASC
 LIMIT 0, 1;

Three executions (193s / 221s / 219s), each examining 141,238,050 rows, matching three loads of the tab in the web server log to the second. The sibling list-table queries on the same page loads each examined ~5.5M rows at ~3s.

Aggravating factors

• Sites without a persistent object cache re-run dokan_get_pending_vendor_count()'s underlying query on admin page loads, since Cache::get() falls back to WP's per-request cache.
• Because each request that abandons (user gives up, nginx logs 499) leaves the query running in MySQL, impatient re-clicks multiply the load.

Expected behaviour

The pending count / pending listing should not scan orders of magnitude more rows than the table holds. Suggestions: don't stack filter_pending_vendors' meta_query on a query that already carries get_vendors()' equivalent group (the badge query on that screen gets both), and/or constrain the joins with meta_key in the ON clause, or derive the count from a keyed query.

Steps to reproduce

1. Marketplace with a few hundred sellers (usermeta in the tens of thousands of rows), no persistent object cache.
2. Log into wp-admin, open Users → Pending Vendor.
3. Observe the WP_User_Query SQL above in the slow log / processlist; runtime grows combinatorially with per-user meta row counts.

[MdAsifHossainNadim]

Release Goes Here.