[wp-trac] [WordPress Trac] #11914: "Users" admin list slow to generate on large sites

[WordPress Trac]

#11914: "Users" admin list slow to generate on large sites
--------------------------+-------------------------------------------------
 Reporter:  rowanbeentje  |       Owner:                        
     Type:  defect (bug)  |      Status:  new                   
 Priority:  low           |   Milestone:  Unassigned            
Component:  Optimization  |     Version:  2.9.1                 
 Severity:  minor         |    Keywords:  users mysql slow query
--------------------------+-------------------------------------------------
 On a site I help administer, the "Users" admin page (wp-admin/users.php)
 can take over 20 seconds to generate, when the server has low load (and
 normally serves pages within a second or two - ie this page is very much
 an outlier).

 The queries getting the post counts for each user are definitely at fault,
 eg in get_usernumposts (although I see there's one or two other
 instances):

         $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM
 $wpdb->posts WHERE post_author = %d AND post_type = 'post' AND ", $userid)
 . get_private_posts_cap_sql('post'));

 There's currently no index on post_author; adding one makes the queries in
 question 5x-10x faster :)

 Other queries using post authors, like viewing an author index, don't seem
 too slow without the index.

 HOWEVER… I should mention this particular site has >30,000 rows in
 wp_posts, and a wp_posts data size of >135MB.  So filing this as "minor"
 issue, and it may not be seen as worth implementing...

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/11914>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software