WordPress.org

Make WordPress Core

Opened 12 years ago

Closed 6 years ago

#5407 closed enhancement (invalid)

Improve wp_list_authors efficiency

Reported by: Bobcat Owned by:
Milestone: Priority: low
Severity: normal Version: 2.3.1
Component: Users Keywords: needs-patch
Focuses: performance Cc:

Description

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.

Attachments (2)

5407.diff (4.6 KB) - added by Bobcat 12 years ago.
Improve wp_list_authors() efficiency
5407_2.diff (4.6 KB) - added by Bobcat 12 years ago.
Improve wp_list_authors() efficiency

Download all attachments as: .zip

Change History (16)

@Bobcat
12 years ago

Improve wp_list_authors() efficiency

@Bobcat
12 years ago

Improve wp_list_authors() efficiency

#1 @Bobcat
12 years ago

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

#2 @ffemtcj
11 years ago

  • Milestone changed from 2.5 to 2.6

#3 @jasonistaken
11 years ago

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.

#4 @jacobsantos
11 years ago

  • Milestone changed from 2.9 to 2.8

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

#5 @jacobsantos
11 years ago

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

#6 @janeforshort
10 years ago

  • Milestone changed from 2.8 to Future Release

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

#7 @Denis-de-Bernardy
10 years ago

  • Keywords needs-patch added; has-patch 2nd-opinion removed
  • Priority changed from normal to low

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.

#10 @takaitra
10 years ago

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

#11 @voyagerfan5761
10 years ago

  • Cc WordPress@… added

#13 @juliobox
6 years ago

  • Cc juliobosk@… added

#14 @nacin
6 years ago

  • Component changed from Optimization to Users
  • Focuses performance added
  • Milestone Future Release deleted
  • Resolution set to invalid
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.