[wp-trac] [WordPress Trac] #29785: User count not correct

[WordPress Trac]

#29785: User count not correct
-------------------------------------------------+-------------------------
 Reporter:  psoluch                              |       Owner:
     Type:  defect (bug)                         |  boonebgorges
 Priority:  normal                               |      Status:  assigned
Component:  Users                                |   Milestone:  Future
 Severity:  normal                               |  Release
 Keywords:  good-first-bug has-unit-tests has-   |     Version:
  patch needs-testing                            |  Resolution:
                                                 |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by tharsheblows):

 I'm not sure on performance testing but here is cheap and cheerful look at
 the MYSQL query performance for 184k users and user meta table with 3.2
 million rows (on average over 17 meta entries per user <sob>).

 `count_users( 'memory' )` is fine with patch.
 {{{
 #!mysql
 #no patch: 0.0813s
 SELECT meta_value FROM wp_usermeta WHERE meta_key = 'wp_capabilities'

 #with patch: 0.0803s
 SELECT meta_value FROM wp_usermeta JOIN wp_users ON user_id = ID WHERE
 meta_key = 'wp_capabilities'
 }}}

 `count_users( 'time' )` has two factors which increase query time:
  - number of users
  - number of roles
 The patch increases the query time substantially for me (~0.6s) when using
 `count_users( 'time' )` with the standard WP roles only and more with an
 increased number of roles.

 `count_users()` is a problem for me already, increasing the query time
 would hurt.

 Here's what I ran, as always please let me know any errors:

 {{{
 #!mysql

 #default WP roles without patch: 1.497s
 SELECT
 COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)),
 COUNT(NULLIF(`meta_value` = 'a:0:{}', false)),
 COUNT(*)
 FROM wp_usermeta
 WHERE meta_key = 'wp_capabilities'

 #default WP roles with patch: 2.100s
 SELECT
 COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)),
 COUNT(NULLIF(`meta_value` = 'a:0:{}', false)),
 COUNT(*)
 FROM wp_usermeta
 JOIN wp_users
 ON user_id = ID
 WHERE meta_key = 'wp_capabilities'

 #including bbPress roles without patch: 2.289s
 SELECT
 COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_keymaster\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_moderator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_participant\"%',false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_spectator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_blocked\"%', false)),
 COUNT(NULLIF(`meta_value` = 'a:0:{}', false)),
 COUNT(*)
 FROM wp_usermeta
 WHERE meta_key = 'wp_capabilities'

 #including bbPress roles with patch: 2.945s
 SELECT
 COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_keymaster\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_spectator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_blocked\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_moderator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_participant\"%', false)),
 COUNT(NULLIF(`meta_value` = 'a:0:{}', false)),
 COUNT(*)
 FROM wp_usermeta
 JOIN wp_users
 ON user_id = ID
 WHERE meta_key ='wp_capabilities'
 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29785#comment:19>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform