[wp-trac] [WordPress Trac] #10329: sort_by and count_limit options for wp_list_authors

[WordPress Trac]

#10329: sort_by and count_limit options for wp_list_authors
-----------------------------+----------------------------------------------
 Reporter:  takaitra         |       Owner:  westi      
     Type:  feature request  |      Status:  reviewing  
 Priority:  normal           |   Milestone:  3.1        
Component:  Template         |     Version:             
 Severity:  normal           |    Keywords:  needs-patch
-----------------------------+----------------------------------------------
Changes (by Denis-de-Bernardy):

  * keywords:  has-patch => needs-patch
  * milestone:  3.0 => 3.1


Comment:

 before patch (each adds a whole bunch of extra queries in addition to the
 signature):

 {{{
 wp_list_authors(array(
         'optioncount' => false, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => false,
         ));

 SELECT DISTINCT post_author, COUNT(ID) AS count FROM www_posts WHERE
 post_type = 'post' AND (post_status = 'publish' OR post_status =
 'private') GROUP BY post_author

 7.8ms

 SELECT * FROM users WHERE ID = 12 LIMIT 1

 0.2ms times as many authors

 SELECT meta_key, meta_value FROM usermeta WHERE user_id = 12

 0.3ms times as many authors

 wp_list_authors(array(
         'optioncount' => true, 'exclude_admin' => false,
         'show_fullname' => true, 'hide_empty' => true,
         ));

 SELECT user_id, user_id AS ID, user_login, display_name, user_email,
 meta_value FROM users, usermeta WHERE users.ID = usermeta.user_id AND
 meta_key = 'www_capabilities' ORDER BY usermeta.user_id

 1.0ms

 SELECT ID, user_nicename from users WHERE ID IN(1,6,10,11,12) ORDER BY
 display_name

 0.3ms can be much larger with many authors

 and then the same queries as above

 }}}

 after patch:

 {{{
 wp_list_authors(array(
         'optioncount' => false, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => false,
         'orderby' => 'name', 'order' => 'ASC', 'min_count' => false
         ));

                 SELECT users.ID, users.user_nicename, users.display_name
 as author_name
                 FROM users


                 JOIN usermeta AS meta_capabilities ON
 meta_capabilities.user_id = users.ID AND meta_capabilities.meta_key =
 'www_capabilities'



                 ORDER BY author_name ASC

 1.0ms


 wp_list_authors(array(
         'optioncount' => true, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => false,
         'orderby' => 'name', 'order' => 'ASC', 'min_count' => false
         ));


                 SELECT users.ID, users.user_nicename, users.display_name
 as author_name, COUNT(www_posts.ID) as author_count
                 FROM users
                 LEFT JOIN www_posts ON www_posts.post_author = users.ID

                 JOIN usermeta AS meta_capabilities ON
 meta_capabilities.user_id = users.ID AND meta_capabilities.meta_key =
 'www_capabilities'
                 WHERE www_posts.post_type = 'post' AND
 www_posts.post_status = 'publish'
                 GROUP BY users.ID

                 ORDER BY author_name ASC

 33.0ms

 wp_list_authors(array(
         'optioncount' => true, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => true,
         'orderby' => 'name', 'order' => 'ASC', 'min_count' => false
         ));

                 SELECT users.ID, users.user_nicename, users.display_name
 as author_name, COUNT(www_posts.ID) as author_count
                 FROM users
                 JOIN www_posts ON www_posts.post_author = users.ID


                 WHERE www_posts.post_type = 'post' AND
 www_posts.post_status = 'publish'
                 GROUP BY users.ID
                 HAVING author_count >= 1
                 ORDER BY author_name ASC

 8.5ms

 wp_list_authors(array(
         'optioncount' => true, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => false,
         'orderby' => 'count', 'order' => 'DESC', 'min_count' => false
         ));

                 SELECT users.ID, users.user_nicename, users.display_name
 as author_name, COUNT(www_posts.ID) as author_count
                 FROM users
                 LEFT JOIN www_posts ON www_posts.post_author = users.ID

                 JOIN usermeta AS meta_capabilities ON
 meta_capabilities.user_id = users.ID AND meta_capabilities.meta_key =
 'www_capabilities'
                 WHERE www_posts.post_type = 'post' AND
 www_posts.post_status = 'publish'
                 GROUP BY users.ID

                 ORDER BY author_count DESC, author_name

 33.0ms

 wp_list_authors(array(
         'optioncount' => true, 'exclude_admin' => false,
         'show_fullname' => false, 'hide_empty' => false,
         'orderby' => 'count', 'order' => 'DESC', 'min_count' => 1
         ));

                 SELECT users.ID, users.user_nicename, users.display_name
 as author_name, COUNT(www_posts.ID) as author_count
                 FROM users
                 JOIN www_posts ON www_posts.post_author = users.ID


                 WHERE www_posts.post_type = 'post' AND
 www_posts.post_status = 'publish'
                 GROUP BY users.ID
                 HAVING author_count >= 1
                 ORDER BY author_count DESC, author_name

 8.1ms

 }}}

 so basically... punting back to future pending more massaging. further
 tests reveal that:

  - the group by tends to hinder performance because it introduces a
 unnecessary sort in MySQL; it can be removed entirely
  - the author name logic would actually be better handled by mass-querying
 the usermeta table using the functions from miqrogroove in a separate
 ticket

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