• 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

    (@olliejones)

    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.

    > 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 is able to pin-point some existing open performance issues:

    https://core.trac.wordpress.org/ticket/28160 seems like a good match here.

    https://core.trac.wordpress.org/ticket/23373 should get a downvote for proposing a clearly suboptimal wildcard search.

    Plugin Author OllieJones

    (@olliejones)

    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

    (@cbratschi)

    The new capability fields were added in WordPress 5.9:

    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

    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

    (@olliejones)

    I am on this, with a plugin under development.

    Plugin Author OllieJones

    (@olliejones)

    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/

    Thread Starter Christoph Bratschi

    (@cbratschi)

    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.