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.

Improve wp_list_authors() efficiency

Improve wp_list_authors() efficiency

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

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.

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

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

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

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.

see also #8648

see #10201

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

see #12014