[wp-trac] [WordPress Trac] #14572: post_author_meta_box causes fatal error on site with large userbase.

[WordPress Trac]

#14572: post_author_meta_box causes fatal error on site with large userbase.
----------------------------+-----------------------------------------------
 Reporter:  tomdebruin      |       Owner:  scribu          
     Type:  defect (bug)    |      Status:  assigned        
 Priority:  normal          |   Milestone:  3.1             
Component:  Administration  |     Version:  3.0.1           
 Severity:  normal          |    Keywords:  needs-patch gsoc
----------------------------+-----------------------------------------------

Comment(by mdawaffe):

 This is not a good query for a multisite with a large number of users.
 For example, make.wordpress.org/ui runs the P2 theme.  The theme runs
 get_users_of_blog() on every blog-side page load during some content
 filters.  That means on every page load, WP_User_Query gets run.

 Old get_users_of_blog() query:

 {{{
 EXPLAIN
 SELECT user_id, user_id AS ID, user_login,
   display_name, user_email, meta_value
 FROM minibb_users, minibb_usermeta
 WHERE minibb_users.ID = minibb_usermeta.user_id
 AND meta_key = 'wporg_5_capabilities'
 ORDER BY minibb_usermeta.user_id
 }}}

 || id || select_type || table || type || possible_keys || key || key_len
 || ref || rows || Extra ||
 || 1 || SIMPLE || minibb_usermeta || ref || user_id,meta_key || meta_key
 || 256 || const || 10 || Using where; Using filesort ||
 || 1 || SIMPLE || minibb_users || eq_ref || PRIMARY || PRIMARY || 8 ||
 wordpress.minibb_usermeta.user_id || 1 || Using where ||


 New WP_User_Query + _wp_meta_sql query:

 {{{
 EXPLAIN
 SELECT DISTINCT(minibb_users.ID) FROM minibb_users
 WHERE 1=1 AND minibb_users.ID IN (
   SELECT user_id FROM minibb_usermeta
   WHERE CASE meta_key
     WHEN 'wporg_5_capabilities' THEN meta_value IS NOT NULL
   END
   GROUP BY user_id HAVING COUNT(*) = 1
 ) ORDER BY user_login ASC;
 Rows: 2
 }}}

 || id || select_type || table || type || possible_keys || key || key_len
 || ref || rows || Extra ||
 || 1  || PRIMARY || minibb_users || index || NULL || user_login || 60 ||
 NULL || 1283835 || Using where; Using index ||
 || 2  || DEPENDENT SUBQUERY || minibb_usermeta || index || NULL || user_id
 || 8 || NULL || 6165217 || Using where ||

 So we traded a filesort on 10 rows for a full table scan on both users and
 usermeta tables.  Not worth it :)

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/14572#comment:22>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software