Changeset 13576

Timestamp:

03/03/2010 07:08:30 PM (15 years ago)

Author:

ryan

Message:

Improve user listing performance. Props miqrogroove. see #11914

Location:

trunk

Files:

• wp-admin/includes/schema.php (modified) (1 diff)
• wp-admin/includes/template.php (modified) (2 diffs)
• wp-admin/users.php (modified) (3 diffs)
• wp-includes/author-template.php (modified) (1 diff)
• wp-includes/pluggable.php (modified) (1 diff)
• wp-includes/post.php (modified) (3 diffs)
• wp-includes/user.php (modified) (6 diffs)

trunk/wp-admin/includes/schema.php

@@ -147 +147 @@
   KEY post_name (post_name),
   KEY type_status_date (post_type,post_status,post_date,ID),
-  KEY post_parent (post_parent)
+  KEY post_parent (post_parent),
+  KEY post_author (post_author)
 ) $charset_collate;
 CREATE TABLE $wpdb->users (

trunk/wp-admin/includes/template.php

@@ -1825 +1825 @@
 
 /**
- * {@internal Missing Short Description}}
- *
- * @since unknown
- *
- * @param unknown_type $user_object
- * @param unknown_type $style
- * @param unknown_type $role
- * @return unknown
- */
-function user_row( $user_object, $style = '', $role = '' ) {
+ * Generate HTML for a single row on the users.php admin panel.
+ *
+ * @since 2.1.0
+ *
+ * @param object $user_object
+ * @param string $style Optional. Attributes added to the TR element.  Must be sanitized.
+ * @param string $role Key for the $wp_roles array.
+ * @param int $numposts Optional. Post count to display for this user.  Defaults to zero, as in, a new user has made zero posts.
+ * @return string
+ */
+function user_row( $user_object, $style = '', $role = '', $numposts = 0 ) {
     global $wp_roles;
 
@@ -1850 +1851 @@
     if ( strlen( $short_url ) > 35 )
         $short_url = substr( $short_url, 0, 32 ).'...';
-    $numposts = get_usernumposts( $user_object->ID );
     $checkbox = '';
     // Check if the user for this row is editable

trunk/wp-admin/users.php

@@ -209 +209 @@
     $role = isset($_GET['role']) ? $_GET['role'] : null;
 
-    // Query the users
+    // Query the user IDs for this page
     $wp_user_search = new WP_User_Search($usersearch, $userspage, $role);
+
+    // Query the post counts for this page
+    $post_counts = count_many_users_posts($wp_user_search->get_results());
+
+    // Query the users for this page
+    cache_users($wp_user_search->get_results());
 
     $messages = array();
@@ -264 +270 @@
 <ul class="subsubsub">
 <?php
-$role_links = array();
-$avail_roles = array();
-$users_of_blog = get_users_of_blog();
-$total_users = count( $users_of_blog );
-foreach ( (array) $users_of_blog as $b_user ) {
-    $b_roles = unserialize($b_user->meta_value);
-    foreach ( (array) $b_roles as $b_role => $val ) {
-        if ( !isset($avail_roles[$b_role]) )
-            $avail_roles[$b_role] = 0;
-        $avail_roles[$b_role]++;
-    }
-}
+$users_of_blog = count_users();
+$total_users = $users_of_blog['total_users'];
+$avail_roles =& $users_of_blog['avail_roles'];
 unset($users_of_blog);
 
 $current_role = false;
 $class = empty($role) ? ' class="current"' : '';
+$role_links = array();
 $role_links[] = "<li><a href='users.php'$class>" . sprintf( _nx( 'All <span class="count">(%s)</span>', 'All <span class="count">(%s)</span>', $total_users, 'users' ), number_format_i18n( $total_users ) ) . '</a>';
 foreach ( $wp_roles->get_names() as $this_role => $name ) {
@@ -373 +371 @@
 
     $style = ( ' class="alternate"' == $style ) ? '' : ' class="alternate"';
-    echo "\n\t" . user_row($user_object, $style, $role);
+    echo "\n\t", user_row($user_object, $style, $role, $post_counts[(string)$userid]);
 }
 ?>

trunk/wp-includes/author-template.php

@@ -152 +152 @@
  * @since 1.5
  * @uses $post The current post in the Loop's DB object.
- * @uses get_usernumposts()
+ * @uses count_user_posts()
  * @return int The number of posts by the author.
  */
 function get_the_author_posts() {
     global $post;
-    return get_usernumposts($post->post_author);
+    return count_user_posts($post->post_author);
 }
 

trunk/wp-includes/pluggable.php

@@ -137 +137 @@
 
     return $user;
+}
+endif;
+
+if ( !function_exists('cache_users') ) :
+/**
+ * Retrieve info for user lists to prevent multiple queries by get_userdata()
+ *
+ * @since 3.0.0
+ *
+ * @param array $users User ID numbers list
+ */
+function cache_users( $users ) {
+    global $wpdb;
+
+    $clean = array();
+    foreach($users as $id) {
+        $id = (int) $id;
+        if (wp_cache_get($id, 'users')) {
+            // seems to be cached already
+        } else {
+            $clean[] = $id;
+        }
+    }
+
+    if ( 0 == count($clean) )
+        return;
+
+    $list = implode(',', $clean);
+
+    $results = $wpdb->get_results("SELECT * FROM $wpdb->users WHERE ID IN ($list)");
+    
+    _fill_many_users($results);
 }
 endif;

trunk/wp-includes/post.php

@@ -3653 +3653 @@
  */
 function get_private_posts_cap_sql($post_type) {
-    global $user_ID;
-    $cap = '';
+    return get_posts_by_author_sql($post_type, FALSE);
+}
+
+/**
+ * Retrieve the post SQL based on capability, author, and type.
+ *
+ * See above for full description.
+ *
+ * @since 3.0.0
+ * @param string $post_type currently only supports 'post' or 'page'.
+ * @param bool $full Optional.  Returns a full WHERE statement instead of just an 'andalso' term.
+ * @param int $post_author Optional.  Query posts having a single author ID.
+ * @return string SQL WHERE code that can be added to a query.
+ */
+function get_posts_by_author_sql($post_type, $full = TRUE, $post_author = NULL) {
+    global $user_ID, $wpdb;
 
     // Private posts
@@ -3664 +3678 @@
     // Dunno what it is, maybe plugins have their own post type?
     } else {
+        $cap = '';
         $cap = apply_filters('pub_priv_sql_capability', $cap);
 
@@ -3669 +3684 @@
             // We don't know what it is, filters don't change anything,
             // so set the SQL up to return nothing.
-            return '1 = 0';
-        }
-    }
-
-    $sql = '(post_status = \'publish\'';
+            return ' 1 = 0 ';
+        }
+    }
+
+    if ($full) {
+        if (is_null($post_author)) {
+            $sql = $wpdb->prepare('WHERE post_type = %s AND ', $post_type);
+        } else {
+            $sql = $wpdb->prepare('WHERE post_author = %d AND post_type = %s AND ', $post_author, $post_type);
+        }
+    } else {
+        $sql = '';
+    }
+
+    $sql .= "(post_status = 'publish'";
 
     if (current_user_can($cap)) {
         // Does the user have the capability to view private posts? Guess so.
-        $sql .= ' OR post_status = \'private\'';
+        $sql .= " OR post_status = 'private'";
     } elseif (is_user_logged_in()) {
         // Users can view their own private posts.
-        $sql .= ' OR post_status = \'private\' AND post_author = \'' . $user_ID . '\'';
-    }
+        $id = (int) $user_ID;
+        if (is_null($post_author) || !$full) {
+            $sql .= " OR post_status = 'private' AND post_author = $id";
+        } elseif ($id == (int)$post_author) {
+            $sql .= " OR post_status = 'private'";
+        } // else none
+    } // else none
 
     $sql .= ')';

trunk/wp-includes/user.php

@@ -149 +149 @@
  * @return int Amount of posts user has written.
  */
-function get_usernumposts($userid) {
+function count_user_posts($userid) {
     global $wpdb;
-    $userid = (int) $userid;
-    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->posts WHERE post_author = %d AND post_type = 'post' AND ", $userid) . get_private_posts_cap_sql('post'));
+
+    $where = get_posts_by_author_sql('post', TRUE, $userid);
+
+    $count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->posts $where" );
+
     return apply_filters('get_usernumposts', $count, $userid);
+}
+
+/**
+ * Number of posts written by a list of users.
+ *
+ * @since 3.0.0
+ * @param array $userid User ID number list.
+ * @return array Amount of posts each user has written.
+ */
+function count_many_users_posts($users) {
+    global $wpdb;
+    
+    if (0 == count($users))
+        return array();
+        
+    $userlist = implode(',', $users);
+    $where = get_posts_by_author_sql('post');
+
+    $result = $wpdb->get_results( "SELECT post_author, COUNT(*) FROM $wpdb->posts $where AND post_author IN ($userlist) GROUP BY post_author", ARRAY_N );
+
+    $count = array();
+    foreach($result as $row) {
+        $count[$row[0]] = $row[1];
+    }
+
+    foreach($users as $id) {
+        $id = (string) $id;
+        if (!isset($count[$id]))
+            $count[$id] = 0;
+    }
+
+    return $count;
 }
 
@@ -341 +376 @@
 function update_user_meta($user_id, $meta_key, $meta_value, $prev_value = '') {
     return update_metadata('user', $user_id, $meta_key, $meta_value, $prev_value);
+}
+
+/**
+ * Count number of users who have each of the user roles.
+ *
+ * Assumes there are neither duplicated nor orphaned capabilities meta_values.
+ * Assumes role names are unique phrases.  Same assumption made by WP_User_Search::prepare_query()
+ * Using $strategy = 'time' this is CPU-intensive and should handle around 10^7 users.
+ * Using $strategy = 'memory' this is memory-intensive and should handle around 10^5 users, but see WP Bug #12257.
+ *
+ * @since 3.0.0
+ * @param string $strategy 'time' or 'memory'
+ * @return array Includes a grand total and an array of counts indexed by role strings.
+ */
+function count_users($strategy = 'time') {
+    global $wpdb, $blog_id, $wp_roles;
+
+    // Initialize
+    $id = (int) $blog_id;
+    $blog_prefix = $wpdb->get_blog_prefix($id);
+    $result = array();
+
+    if ('time' == $strategy) {
+        $avail_roles = $wp_roles->get_names();
+
+        // Build a CPU-intensive query that will return concise information.
+        $select_count = array();
+        foreach ( $avail_roles as $this_role => $name ) {
+            $select_count[] = "COUNT(NULLIF(`meta_value` LIKE '%" . like_escape($this_role) . "%', FALSE))";
+        }
+        $select_count = implode(', ', $select_count);
+
+        // Add the meta_value index to the selection list, then run the query.
+        $row = $wpdb->get_row( "SELECT $select_count, COUNT(*) FROM $wpdb->usermeta WHERE meta_key = '{$blog_prefix}capabilities'", ARRAY_N );
+
+        // Run the previous loop again to associate results with role names.
+        $col = 0;
+        $role_counts = array();
+        foreach ( $avail_roles as $this_role => $name ) {
+            $count = (int) $row[$col++];
+            if ($count > 0) {
+                $role_counts[$this_role] = $count;
+            }
+        }
+
+        // Get the meta_value index from the end of the result set.
+        $total_users = (int) $row[$col];
+
+        $result['total_users'] = $total_users;
+        $result['avail_roles'] =& $role_counts;
+    } else {
+        $avail_roles = array();
+
+        $users_of_blog = $wpdb->get_col( "SELECT meta_value FROM $wpdb->usermeta WHERE meta_key = '{$blog_prefix}capabilities'" );
+
+        foreach ( $users_of_blog as $caps_meta ) {
+            $b_roles = unserialize($caps_meta);
+            if ( is_array($b_roles) ) {
+                foreach ( $b_roles as $b_role => $val ) {
+                    if ( isset($avail_roles[$b_role]) ) {
+                        $avail_roles[$b_role]++;
+                    } else {
+                        $avail_roles[$b_role] = 1;
+                    }
+                }
+            }
+        }
+
+        $result['total_users'] = count( $users_of_blog );
+        $result['avail_roles'] =& $avail_roles;
+    }
+
+    return $result;
 }
 
@@ -499 +607 @@
  * The finished user data is cached, but the cache is not used to fill in the
  * user data for the given object. Once the function has been used, the cache
- * should be used to retrieve user data. The purpose seems then to be to ensure
- * that the data in the object is always fresh.
+ * should be used to retrieve user data. The intention is if the current data
+ * had been cached already, there would be no need to call this function.
  *
  * @access private
@@ -509 +617 @@
  */
 function _fill_user( &$user ) {
+    $metavalues = get_user_metavalues(array($user->ID));
+    _fill_single_user($user, $metavalues[$user->ID]);
+}
+
+/**
+ * Perform the query to get the $metavalues array(s) needed by _fill_user and _fill_many_users
+ *
+ * @since 3.0.0
+ * @param array $ids User ID numbers list.
+ * @return array of arrays. The array is indexed by user_id, containing $metavalues object arrays.
+ */
+function get_user_metavalues($ids) {
     global $wpdb;
 
+    $clean = array_map('intval', $ids);
+    if ( 0 == count($clean) )
+        return $objects;
+
+    $list = implode(',', $clean);
+
     $show = $wpdb->hide_errors();
-    $metavalues = $wpdb->get_results($wpdb->prepare("SELECT meta_key, meta_value FROM $wpdb->usermeta WHERE user_id = %d", $user->ID));
+    $metavalues = $wpdb->get_results("SELECT user_id, meta_key, meta_value FROM $wpdb->usermeta WHERE user_id IN ($list)");
     $wpdb->show_errors($show);
 
-    if ( $metavalues ) {
-        foreach ( (array) $metavalues as $meta ) {
-            $value = maybe_unserialize($meta->meta_value);
-            $user->{$meta->meta_key} = $value;
-        }
+    $objects = array();
+    foreach($clean as $id) {
+        $objects[$id] = array();
+    }
+    foreach($metavalues as $meta_object) {
+        $objects[$meta_object->user_id][] = $meta_object;
+    }
+
+    return $objects;
+}
+
+/**
+ * Unserialize user metadata, fill $user object, then cache everything.
+ *
+ * @since 3.0.0
+ * @param object $user The User object.
+ * @param array $metavalues An array of objects provided by get_user_metavalues()
+ */
+function _fill_single_user( &$user, &$metavalues ) {
+    global $wpdb;
+
+    foreach ( $metavalues as $meta ) {
+        $value = maybe_unserialize($meta->meta_value);
+        $user->{$meta->meta_key} = $value;
     }
 
@@ -534 +679 @@
         $user->user_description = $user->description;
 
-    wp_cache_add($user->ID, $user, 'users');
-    wp_cache_add($user->user_login, $user->ID, 'userlogins');
-    wp_cache_add($user->user_email, $user->ID, 'useremail');
-    wp_cache_add($user->user_nicename, $user->ID, 'userslugs');
+    update_user_caches($user);
+}
+
+/**
+ * Take an array of user objects, fill them with metas, and cache them.
+ *
+ * @since 3.0.0
+ * @param array $users User objects
+ * @param array $metas User metavalues objects
+ */
+function _fill_many_users( &$users ) {
+    $ids = array();
+    foreach($users as $user_object) {
+        $ids[] = $user_object->ID;
+    }
+
+    $metas = get_user_metavalues($ids);
+
+    foreach($users as $user_object) {
+        if (isset($metas[$user_object->ID])) {
+            _fill_single_user($user_object, $metas[$user_object->ID]);
+        }
+    }
 }
 
@@ -657 +821 @@
 
 /**
+ * Update all user caches
+ *
+ * @since 3.0.0
+ *
+ * @param object $user User object to be cached
+ */
+function update_user_caches(&$user) {
+    wp_cache_add($user->ID, $user, 'users');
+    wp_cache_add($user->user_login, $user->ID, 'userlogins');
+    wp_cache_add($user->user_email, $user->ID, 'useremail');
+    wp_cache_add($user->user_nicename, $user->ID, 'userslugs');
+}
+
+/**
  * Clean all user caches
  *
- * @since 3.0
+ * @since 3.0.0
  *
  * @param int $id User ID
- * @return void
  */
 function clean_user_cache($id) {