Support » Developing with WordPress » wp_usermeta ~ 3 milion wor has slowly query.

  • I have ~ 200k users, and ~ 3 milion row in usermeta, and when have query inner join user_meta for search by key has very slowly, what problem and how fix it?

    I use Query Monitor:
    _ Loaded /wp-admin/edit.php?post_type=page ~ 20s.
    _ Query trigger slowly

    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_user_level'
    AND wp_usermeta.meta_value != '0' ) )
    ORDER BY display_name ASC
    	
    + Toggle button
    WP_User_Query->query()	9.4515
    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_user_level'
    AND wp_usermeta.meta_value != '0' ) )
    ORDER BY display_name ASC	
    + Toggle button
    WP_User_Query->query()	8.1939

    The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Support Volunteer

    You might try adding indexes on any columns used in these WHERE and ORDERBY clauses (if they are not already indexed). It will increase the size of the table but decrease the time for the query. Also, consider switching your tables from MyISAM to InnoDB.

    i will try it but i dont understand why have one query all user and join with meta data when go to page and post. If data small no problem, if data large is very terrible, this query from to core, why not limit or detect? I think have some change here if we want to use large database with wordpress.

    i see function get_users() in wp-include/user.php.

    • This reply was modified 4 months, 2 weeks ago by  goben2489.
    Moderator Steven Stern (sterndata)

    (@sterndata)

    Support Volunteer

    I’m suggesting some database optimizations that may help with this.

    There isn’t an index on the meta_value column of the WP core _usermeta table, and that is causing your queries to be slow. An index would solve the problem, but you can’t add an index to a WP core table because the dbDelta() function will remove the index the next time you update WordPress.

    I’d suggest finding another way to accomplish what you want without using wp_usermeta.meta_value in a WHERE clause in your query.

    Is this really the query you need ? Your query is fetching every userID + name etc for every user with a user_level not zero. More likely you need to know these details for the current user, or even this for a limited list of users.

    Steven’s suggestion is quite viable, you would have to be prepared to recreate the index after an upgrade and the performance degrades.

    Even if one added code to restore the index after an update, it would be a bad idea to add an index on the meta_value column. The column is defined as LONGTEXT, and the table contains 3 million rows. That combination would result in the index taking a long time to add/delete (perhaps causing the DB update to time out, which would be a disaster), and the index could increase the size of the _usermeta table up to an order of magnitude (though more likely in the 2X-3X range).

    Querying on the option_value column in the _options table, and the meta_value column in the four _????meta tables, is unfortunately fairly common. It’s no doubt a reason why many themes and plugins are “heavy”. There should be a warning in the Codex about this…

Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.