Improve wp_list_authors efficiency

[Bobcat]

wp_list_authors() makes multiple queries per registered user. For example, if a blog has 50 registered users, wp_list_authors() will make over 100 queries, even if there are only a few users who can write posts.

Alexander Concha developed the attached patch which does it all with only one query total (two if "show_fullname" is enabled). However, he questions if this change belongs in the WP core. I think it does because it's such a vast improvement over the current extremely inefficient code.

[Bobcat]

Improve wp_list_authors() efficiency

[Bobcat]

Improve wp_list_authors() efficiency

[Bobcat]

Updated patch for latest trunk version of author-template.php

[jasonistaken]

I would say this should be a pretty high priority.
I just ran into an instance with a site that has over 13,000 registered subscribers, and wp_list_authors in its current state wants to use over 80Mb of memory.

[jacobsantos]

If it works, then it works and should be included.

[jacobsantos]

Why not include it early for 2.8, so that if there are any problems, the change can either be reverted or corrected?

[janeforshort]

Pushing due to schedule. Can evaluate in next release cycle.

[Denis-de-Bernardy]

there are still inefficiencies in the suggested patch. the correct one to use is:

	if ( $show_fullname ) {
		$author_name_field = "CASE
		WHEN TRIM(CONCAT(COALESCE(meta_first_name.meta_value, ''), COALESCE(meta_last_name.meta_value, ''))) <> ''
		THEN
			TRIM(CONCAT(COALESCE(meta_first_name.meta_value, ''), COALESCE(meta_last_name.meta_value, '')))
		ELSE
			$wpdb->users.display_name
		END"
		$author_name_join = "
		LEFT JOIN $wpdb->usermeta as meta_first_name ON meta_first_name.user_id = $wpdb->users.ID AND meta_first_name.meta_key = 'firstname'
		LEFT JOIN $wpdb->usermeta as meta_last_name ON meta_last_name.user_id = $wpdb->users.ID AND meta_last_name.meta_key = 'lastname'
		";
	} else {
		$author_name_field = "$wpdb->users.display_name"
		$author_name_join = "";
	}
	
	$authors = $wpdb->get_results("
		SELECT $wpdb->users.ID, $wpdb->users.user_nicename, COUNT($wpdb->posts.ID) as author_count, $author_name_field as author_name
		FROM $wpdb->users
		" . ( !$hide_empty ? "LEFT " : '' ) . "JOIN $wpdb->posts ON $wpdb->posts.post_author = $wpdb->users.ID
		$author_name_join
		" . ( $exclude_admin ? "WHERE $wpdb->users.user_login <> 'admin' " : '' ) . "
		GROUP BY $wpdb->users.ID
		ORDER BY $wpdb->users.display_name
		");

It'll then get all of the needed details in one go, without the clutter (why is the private_posts_sql in there anyway?).

However, it's mostly pointless to do so without editing a few more functions. Specifically, get_author_feed_link() will then go on for a rampage because it'll get each author individually. an extra param (user_nicename) is needed to the function, in order to pass it to get_author_posts_url().

the rest amounts to needing to rewrite the function as needed.

[Denis-de-Bernardy]

see also #8648

[Denis-de-Bernardy]

see #10201

[takaitra]

Patch submitted to a related ticket including Denis-de-Bernardy's suggested code above. See #10329.

[Denis-de-Bernardy]

see #12014

[nacin]

None of this is raw SQL anymore — it uses get_users() for caching purposes.