Authors query slow on large sites

We’re seeing the same issue as reported in Alter authors query to improve slow lookup on blogs with many users (now closed for inactivity). On a multisite with 3000 users page loads take ten seconds. I’ve adapted aaronerk’s patch for Authors 2.3. I can’t seem to post the patch so I’m pasting the diff below.


diff --git a/authors.php b/authors.php
index b2c7a67..1a44111 100644
--- a/authors.php
+++ b/authors.php
@@ -86,8 +86,6 @@ if ( function_exists('register_sidebar_widget') ) :

if ( function_exists('seo_tag_cloud_generate') ) :
function widget_authors_cloud($args = '') {
- global $wpdb;
-
$defaults = array(
'optioncount' => false, 'exclude_admin' => true,
'show_fullname' => false, 'hide_empty' => true,
@@ -103,19 +101,8 @@ if ( function_exists('register_sidebar_widget') ) :

$return = '';

- if (empty($include)) {
- $include_sql = '';
- $exclude_sql = ' AND ID NOT IN (' . $exclude . ') AND user_login NOT IN (' . $exclude . ')';
- } else {
- $include_sql = ' AND (ID IN (' . $include . ') OR user_login IN (' . $include . '))';
- $exclude_sql = '';
- }
- $authors = $wpdb->get_results('SELECT ID, user_nicename, display_name FROM ' . $wpdb->users . ' WHERE 0=0' . ($exclude_admin ? ' AND ID <> 1' : '') . $exclude_sql . $include_sql . ' ORDER BY display_name');
-
- $author_count = array();
- foreach ((array) $wpdb->get_results('SELECT DISTINCT post_author, COUNT(ID) AS count FROM '.$wpdb->posts.' WHERE post_type = "post" AND ' . get_private_posts_cap_sql( 'post' ) . ' GROUP BY post_author') as $row) {
- $author_count[$row->post_author] = $row->count;
- }
+ $authors = widget_authors_get_authors( $include, $exclude );
+ $author_count = widget_authors_count_authors ( $authors );

widget_authors_sort_by($orderby, $authors);

@@ -123,6 +110,8 @@ if ( function_exists('register_sidebar_widget') ) :
$posts = (isset($author_count[$author->ID])) ? $author_count[$author->ID] : 0;
if ( $posts != 0 || !$hide_empty ) {
$author = get_userdata( $author->ID );
+ if ($exclude_admin && 10 == $author->user_level)
+ continue;
$name = $author->display_name;
if ( $show_fullname && ($author->first_name != '' && $author->last_name != '') )
$name = $author->first_name . ' ' . $author->last_name;
@@ -201,8 +190,6 @@ if ( function_exists('register_sidebar_widget') ) :
* @return null|string The output, if echo is set to false.
*/
function widget_authors_list_authors($args = '') {
- global $wpdb;
-
$defaults = array(
'optioncount' => false, 'exclude_admin' => true,
'show_fullname' => false, 'hide_empty' => true,
@@ -218,20 +205,8 @@ function widget_authors_list_authors($args = '') {
extract($r, EXTR_SKIP);
$return = '';

- if (empty($include)) {
- $include_sql = '';
- $exclude_sql = ' AND ID NOT IN (' . $exclude . ') AND user_login NOT IN (' . $exclude . ')';
- } else {
- $include_sql = ' AND (ID IN (' . $include . ') OR user_login IN (' . $include . '))';
- $exclude_sql = '';
- }
- /** @todo Move select to get_authors(). */
- $authors = $wpdb->get_results('SELECT ID, user_nicename FROM ' . $wpdb->users . ' WHERE 0=0' . ($exclude_admin ? ' AND ID <> 1' : '') . $exclude_sql . $include_sql . ' ORDER BY display_name');
-
- $author_count = array();
- foreach ((array) $wpdb->get_results('SELECT DISTINCT post_author, COUNT(ID) AS count FROM ' . $wpdb->posts . ' WHERE post_type = "post" AND ' . get_private_posts_cap_sql('post') . ' GROUP BY post_author') as $row) {
- $author_count[$row->post_author] = $row->count;
- }
+ $authors = widget_authors_get_authors( $include, $exclude);
+ $author_count = widget_authors_count_authors ( $authors );

widget_authors_sort_by($orderby, $authors);

@@ -534,6 +509,46 @@ function widget_authors_list_authors($args = '') {
return $newoptions;
}

+ function widget_authors_get_authors( $include = array(), $exclude = array()) {
+ if ( !empty( $include ) ) {
+ $include = explode( ',', $include);
+ foreach ( $include as $key => $val ) {
+ if ( !is_int( $val ) ) {
+ $user = get_user_by( 'login', $val );
+ $include[$key] = $user->ID;
+ }
+ }
+ }
+ if ( !empty( $exclude ) ) {
+ $exclude = explode( ',', $exclude);
+ foreach ( $exclude as $key => $val ) {
+ if ( !is_int( $val ) ) {
+ $user = get_user_by( 'login', $val );
+ $exclude[$key] = $user->ID;
+ }
+ }
+ }
+ $user_args = array(
+ 'fields' => array( 'ID', 'user_nicename', 'display_name' ),
+ 'who' => 'authors',
+ 'orderby' => 'display_name',
+ 'include' => $include,
+ 'exclude' => $exclude
+ );
+ $wp_user_search = new WP_User_Query( $user_args );
+ $authors = $wp_user_search->get_results();
+ return $authors;
+ }
+
+ function widget_authors_count_authors( $authors ) {
+ $author_ids = array();
+ foreach( $authors as $author) {
+ $author_ids[] = $author->ID;
+ }
+ $author_count = count_many_users_posts($author_ids);
+ return $author_count;
+ }
+
if ( !$options = get_option( 'widget_authors' ) )
$options = array();

--
1.8.2.3

http://wordpress.org/plugins/authors/