Title: wp_dropdown_users() Optimization
Last modified: February 25, 2022

---

# wp_dropdown_users() Optimization

 *  Resolved [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/)
 * (@cbratschi)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/)
 * Thanks a lot for this great plugin which adds good optimizations for larger sites.
   After analyzing many different cases on our WordPress installation the following
   query caused by wp_dropdown_users() is very slow with the optimized table structure:
 *     ```
       SELECT wp_users.ID,wp_users.user_login,wp_users.display_name
       FROM wp_users
       INNER JOIN wp_usermeta
       ON ( wp_users.ID = wp_usermeta.user_id )
       WHERE 1=1
       AND ( ( ( ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"edit\\_posts\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"administrator\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"editor\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"author\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"contributor\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"wpseo\\_manager\"%' )
       OR ( wp_usermeta.meta_key = 'wp_capabilities'
       AND wp_usermeta.meta_value LIKE '%\"wpseo\\_editor\"%' ) ) ) )
       ORDER BY display_name ASC
       ```
   
 * We are seeing query times from 4 to 8 seconds with 4 million records in wp_usermeta.
   The queried values should be in an index but something seems to go wrong.
 * Any idea how to optimize this?

Viewing 7 replies - 1 through 7 (of 7 total)

 *  Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/)
 * (@olliejones)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15403651)
 * That’s a truly awful query. A similar one shows up when rendering the Users dashboard,
   to generate the line at the top showing how many Adminstrators / Editors / Authors/
   Subscriber users the site has. With dozens of users, it’s all good. With tens
   of thousands, it collapses.
 * It contains the notorious query-performance antipattern `column LIKE '%something%'`
   with the wild card ‘%’ character at the *beginning* of the match string.
 * There’s no indexing magic in MariaDB / MySQL that can address that. (Postgres
   has a nifty index type called trigram indexing that solves the problem, but WordPress
   doesn’t use Postgres.)
 * I wish I had a good answer for you. I don’t. The WordPress team has a new Performance
   group. I’ll file a bug with them, because this is just ridiculous.
 * Maybe there’s a programming approach (for a plugin) that could solve the problem;
   I’ll do some experiments.
 * Thanks for the report.
 *  [lkraav](https://wordpress.org/support/users/lkraav/)
 * (@lkraav)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15403735)
 * > I wish I had a good answer for you. I don’t. The WordPress team has a new Performance
   group. I’ll file a bug with them, because this is just ridiculous.
 * I think this should go directly into Core issue tracker.
 * [https://core.trac.wordpress.org/search?q=wp_usermeta.meta_key](https://core.trac.wordpress.org/search?q=wp_usermeta.meta_key)
   is able to pin-point some existing open performance issues:
 * [https://core.trac.wordpress.org/ticket/28160](https://core.trac.wordpress.org/ticket/28160)
   seems like a good match here.
 * [https://core.trac.wordpress.org/ticket/23373](https://core.trac.wordpress.org/ticket/23373)
   should get a downvote for proposing a clearly suboptimal wildcard search.
 *  Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/)
 * (@olliejones)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15409079)
 * Cool. I’ll follow up on those tix.
 * I’m working on a new Index WP Users For Speed plugin hopefully to address some
   of these issues. I’ll keep you posted. I suspect using separate usermeta entries
   with meta_names like `wp_capability_seo_manager` might help a lot.
 * I will let you know when the plugin is available.
 *  Thread Starter [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/)
 * (@cbratschi)
 * [4 years, 3 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15410647)
 * The new capability fields were added in WordPress 5.9:
 * > [New Capability Queries in WordPress 5.9](https://make.wordpress.org/core/2022/01/05/new-capability-queries-in-wordpress-5-9/)
 * This is the reason why we did not observe those slow database calls before. Normally
   we are using ElasticPress which uses an external ElasticSearch instance. However,
   the capability fields are not yet supported by ElasticPress:
 * [https://github.com/10up/ElasticPress/issues/2619](https://github.com/10up/ElasticPress/issues/2619)
 * I am now looking for a workaround to cache those quick edit dropdowns. Our users
   with edit rights are not changing often. Unfortunately the WordPress 5.9 changes
   do not use the Object Cache at all, this would have been the best caching solution.
 *  Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/)
 * (@olliejones)
 * [4 years, 2 months ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15491808)
 * I am on this, with a plugin under development.
 *  Plugin Author [OllieJones](https://wordpress.org/support/users/olliejones/)
 * (@olliejones)
 * [4 years, 1 month ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15539949)
 * Here is the newly released plugin, designed specifically to rework the user-lookup
   queries to make them faster. [https://wordpress.org/plugins/index-wp-users-for-speed/](https://wordpress.org/plugins/index-wp-users-for-speed/)
 *  Thread Starter [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/)
 * (@cbratschi)
 * [4 years, 1 month ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15540481)
 * Thanks a lot for your effort this will help a lot of users running large WordPress
   instances.
 * For our case we extended the ElasticPress integration to get the query time down
   to 120 ms. For text searches nothing can easily beat Elasticsearch.

Viewing 7 replies - 1 through 7 (of 7 total)

The topic ‘wp_dropdown_users() Optimization’ is closed to new replies.

 * ![](https://ps.w.org/index-wp-mysql-for-speed/assets/icon-128x128.png?rev=2652667)
 * [Index WP MySQL For Speed](https://wordpress.org/plugins/index-wp-mysql-for-speed/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/index-wp-mysql-for-speed/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/)
 * [Active Topics](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/index-wp-mysql-for-speed/reviews/)

 * 7 replies
 * 3 participants
 * Last reply from: [Christoph Bratschi](https://wordpress.org/support/users/cbratschi/)
 * Last activity: [4 years, 1 month ago](https://wordpress.org/support/topic/wp_dropdown_users-optimization/#post-15540481)
 * Status: resolved