WordPress.org

Make WordPress Core

Changeset 13576


Ignore:
Timestamp:
03/03/2010 07:08:30 PM (10 years ago)
Author:
ryan
Message:

Improve user listing performance. Props miqrogroove. see #11914

Location:
trunk
Files:
7 edited

Legend:

Unmodified
Added
Removed
  • trunk/wp-admin/includes/schema.php

    r13564 r13576  
    147147  KEY post_name (post_name),
    148148  KEY type_status_date (post_type,post_status,post_date,ID),
    149   KEY post_parent (post_parent)
     149  KEY post_parent (post_parent),
     150  KEY post_author (post_author)
    150151) $charset_collate;
    151152CREATE TABLE $wpdb->users (
  • trunk/wp-admin/includes/template.php

    r13536 r13576  
    18251825
    18261826/**
    1827  * {@internal Missing Short Description}}
    1828  *
    1829  * @since unknown
    1830  *
    1831  * @param unknown_type $user_object
    1832  * @param unknown_type $style
    1833  * @param unknown_type $role
    1834  * @return unknown
    1835  */
    1836 function user_row( $user_object, $style = '', $role = '' ) {
     1827 * Generate HTML for a single row on the users.php admin panel.
     1828 *
     1829 * @since 2.1.0
     1830 *
     1831 * @param object $user_object
     1832 * @param string $style Optional. Attributes added to the TR element.  Must be sanitized.
     1833 * @param string $role Key for the $wp_roles array.
     1834 * @param int $numposts Optional. Post count to display for this user.  Defaults to zero, as in, a new user has made zero posts.
     1835 * @return string
     1836 */
     1837function user_row( $user_object, $style = '', $role = '', $numposts = 0 ) {
    18371838    global $wp_roles;
    18381839
     
    18501851    if ( strlen( $short_url ) > 35 )
    18511852        $short_url = substr( $short_url, 0, 32 ).'...';
    1852     $numposts = get_usernumposts( $user_object->ID );
    18531853    $checkbox = '';
    18541854    // Check if the user for this row is editable
  • trunk/wp-admin/users.php

    r12916 r13576  
    209209    $role = isset($_GET['role']) ? $_GET['role'] : null;
    210210
    211     // Query the users
     211    // Query the user IDs for this page
    212212    $wp_user_search = new WP_User_Search($usersearch, $userspage, $role);
     213
     214    // Query the post counts for this page
     215    $post_counts = count_many_users_posts($wp_user_search->get_results());
     216
     217    // Query the users for this page
     218    cache_users($wp_user_search->get_results());
    213219
    214220    $messages = array();
     
    264270<ul class="subsubsub">
    265271<?php
    266 $role_links = array();
    267 $avail_roles = array();
    268 $users_of_blog = get_users_of_blog();
    269 $total_users = count( $users_of_blog );
    270 foreach ( (array) $users_of_blog as $b_user ) {
    271     $b_roles = unserialize($b_user->meta_value);
    272     foreach ( (array) $b_roles as $b_role => $val ) {
    273         if ( !isset($avail_roles[$b_role]) )
    274             $avail_roles[$b_role] = 0;
    275         $avail_roles[$b_role]++;
    276     }
    277 }
     272$users_of_blog = count_users();
     273$total_users = $users_of_blog['total_users'];
     274$avail_roles =& $users_of_blog['avail_roles'];
    278275unset($users_of_blog);
    279276
    280277$current_role = false;
    281278$class = empty($role) ? ' class="current"' : '';
     279$role_links = array();
    282280$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>';
    283281foreach ( $wp_roles->get_names() as $this_role => $name ) {
     
    373371
    374372    $style = ( ' class="alternate"' == $style ) ? '' : ' class="alternate"';
    375     echo "\n\t" . user_row($user_object, $style, $role);
     373    echo "\n\t", user_row($user_object, $style, $role, $post_counts[(string)$userid]);
    376374}
    377375?>
  • trunk/wp-includes/author-template.php

    r12699 r13576  
    152152 * @since 1.5
    153153 * @uses $post The current post in the Loop's DB object.
    154  * @uses get_usernumposts()
     154 * @uses count_user_posts()
    155155 * @return int The number of posts by the author.
    156156 */
    157157function get_the_author_posts() {
    158158    global $post;
    159     return get_usernumposts($post->post_author);
     159    return count_user_posts($post->post_author);
    160160}
    161161
  • trunk/wp-includes/pluggable.php

    r13560 r13576  
    137137
    138138    return $user;
     139}
     140endif;
     141
     142if ( !function_exists('cache_users') ) :
     143/**
     144 * Retrieve info for user lists to prevent multiple queries by get_userdata()
     145 *
     146 * @since 3.0.0
     147 *
     148 * @param array $users User ID numbers list
     149 */
     150function cache_users( $users ) {
     151    global $wpdb;
     152
     153    $clean = array();
     154    foreach($users as $id) {
     155        $id = (int) $id;
     156        if (wp_cache_get($id, 'users')) {
     157            // seems to be cached already
     158        } else {
     159            $clean[] = $id;
     160        }
     161    }
     162
     163    if ( 0 == count($clean) )
     164        return;
     165
     166    $list = implode(',', $clean);
     167
     168    $results = $wpdb->get_results("SELECT * FROM $wpdb->users WHERE ID IN ($list)");
     169   
     170    _fill_many_users($results);
    139171}
    140172endif;
  • trunk/wp-includes/post.php

    r13424 r13576  
    36533653 */
    36543654function get_private_posts_cap_sql($post_type) {
    3655     global $user_ID;
    3656     $cap = '';
     3655    return get_posts_by_author_sql($post_type, FALSE);
     3656}
     3657
     3658/**
     3659 * Retrieve the post SQL based on capability, author, and type.
     3660 *
     3661 * See above for full description.
     3662 *
     3663 * @since 3.0.0
     3664 * @param string $post_type currently only supports 'post' or 'page'.
     3665 * @param bool $full Optional.  Returns a full WHERE statement instead of just an 'andalso' term.
     3666 * @param int $post_author Optional.  Query posts having a single author ID.
     3667 * @return string SQL WHERE code that can be added to a query.
     3668 */
     3669function get_posts_by_author_sql($post_type, $full = TRUE, $post_author = NULL) {
     3670    global $user_ID, $wpdb;
    36573671
    36583672    // Private posts
     
    36643678    // Dunno what it is, maybe plugins have their own post type?
    36653679    } else {
     3680        $cap = '';
    36663681        $cap = apply_filters('pub_priv_sql_capability', $cap);
    36673682
     
    36693684            // We don't know what it is, filters don't change anything,
    36703685            // so set the SQL up to return nothing.
    3671             return '1 = 0';
    3672         }
    3673     }
    3674 
    3675     $sql = '(post_status = \'publish\'';
     3686            return ' 1 = 0 ';
     3687        }
     3688    }
     3689
     3690    if ($full) {
     3691        if (is_null($post_author)) {
     3692            $sql = $wpdb->prepare('WHERE post_type = %s AND ', $post_type);
     3693        } else {
     3694            $sql = $wpdb->prepare('WHERE post_author = %d AND post_type = %s AND ', $post_author, $post_type);
     3695        }
     3696    } else {
     3697        $sql = '';
     3698    }
     3699
     3700    $sql .= "(post_status = 'publish'";
    36763701
    36773702    if (current_user_can($cap)) {
    36783703        // Does the user have the capability to view private posts? Guess so.
    3679         $sql .= ' OR post_status = \'private\'';
     3704        $sql .= " OR post_status = 'private'";
    36803705    } elseif (is_user_logged_in()) {
    36813706        // Users can view their own private posts.
    3682         $sql .= ' OR post_status = \'private\' AND post_author = \'' . $user_ID . '\'';
    3683     }
     3707        $id = (int) $user_ID;
     3708        if (is_null($post_author) || !$full) {
     3709            $sql .= " OR post_status = 'private' AND post_author = $id";
     3710        } elseif ($id == (int)$post_author) {
     3711            $sql .= " OR post_status = 'private'";
     3712        } // else none
     3713    } // else none
    36843714
    36853715    $sql .= ')';
  • trunk/wp-includes/user.php

    r13553 r13576  
    149149 * @return int Amount of posts user has written.
    150150 */
    151 function get_usernumposts($userid) {
     151function count_user_posts($userid) {
    152152    global $wpdb;
    153     $userid = (int) $userid;
    154     $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'));
     153
     154    $where = get_posts_by_author_sql('post', TRUE, $userid);
     155
     156    $count = $wpdb->get_var( "SELECT COUNT(*) FROM $wpdb->posts $where" );
     157
    155158    return apply_filters('get_usernumposts', $count, $userid);
     159}
     160
     161/**
     162 * Number of posts written by a list of users.
     163 *
     164 * @since 3.0.0
     165 * @param array $userid User ID number list.
     166 * @return array Amount of posts each user has written.
     167 */
     168function count_many_users_posts($users) {
     169    global $wpdb;
     170   
     171    if (0 == count($users))
     172        return array();
     173       
     174    $userlist = implode(',', $users);
     175    $where = get_posts_by_author_sql('post');
     176
     177    $result = $wpdb->get_results( "SELECT post_author, COUNT(*) FROM $wpdb->posts $where AND post_author IN ($userlist) GROUP BY post_author", ARRAY_N );
     178
     179    $count = array();
     180    foreach($result as $row) {
     181        $count[$row[0]] = $row[1];
     182    }
     183
     184    foreach($users as $id) {
     185        $id = (string) $id;
     186        if (!isset($count[$id]))
     187            $count[$id] = 0;
     188    }
     189
     190    return $count;
    156191}
    157192
     
    341376function update_user_meta($user_id, $meta_key, $meta_value, $prev_value = '') {
    342377    return update_metadata('user', $user_id, $meta_key, $meta_value, $prev_value);
     378}
     379
     380/**
     381 * Count number of users who have each of the user roles.
     382 *
     383 * Assumes there are neither duplicated nor orphaned capabilities meta_values.
     384 * Assumes role names are unique phrases.  Same assumption made by WP_User_Search::prepare_query()
     385 * Using $strategy = 'time' this is CPU-intensive and should handle around 10^7 users.
     386 * Using $strategy = 'memory' this is memory-intensive and should handle around 10^5 users, but see WP Bug #12257.
     387 *
     388 * @since 3.0.0
     389 * @param string $strategy 'time' or 'memory'
     390 * @return array Includes a grand total and an array of counts indexed by role strings.
     391 */
     392function count_users($strategy = 'time') {
     393    global $wpdb, $blog_id, $wp_roles;
     394
     395    // Initialize
     396    $id = (int) $blog_id;
     397    $blog_prefix = $wpdb->get_blog_prefix($id);
     398    $result = array();
     399
     400    if ('time' == $strategy) {
     401        $avail_roles = $wp_roles->get_names();
     402
     403        // Build a CPU-intensive query that will return concise information.
     404        $select_count = array();
     405        foreach ( $avail_roles as $this_role => $name ) {
     406            $select_count[] = "COUNT(NULLIF(`meta_value` LIKE '%" . like_escape($this_role) . "%', FALSE))";
     407        }
     408        $select_count = implode(', ', $select_count);
     409
     410        // Add the meta_value index to the selection list, then run the query.
     411        $row = $wpdb->get_row( "SELECT $select_count, COUNT(*) FROM $wpdb->usermeta WHERE meta_key = '{$blog_prefix}capabilities'", ARRAY_N );
     412
     413        // Run the previous loop again to associate results with role names.
     414        $col = 0;
     415        $role_counts = array();
     416        foreach ( $avail_roles as $this_role => $name ) {
     417            $count = (int) $row[$col++];
     418            if ($count > 0) {
     419                $role_counts[$this_role] = $count;
     420            }
     421        }
     422
     423        // Get the meta_value index from the end of the result set.
     424        $total_users = (int) $row[$col];
     425
     426        $result['total_users'] = $total_users;
     427        $result['avail_roles'] =& $role_counts;
     428    } else {
     429        $avail_roles = array();
     430
     431        $users_of_blog = $wpdb->get_col( "SELECT meta_value FROM $wpdb->usermeta WHERE meta_key = '{$blog_prefix}capabilities'" );
     432
     433        foreach ( $users_of_blog as $caps_meta ) {
     434            $b_roles = unserialize($caps_meta);
     435            if ( is_array($b_roles) ) {
     436                foreach ( $b_roles as $b_role => $val ) {
     437                    if ( isset($avail_roles[$b_role]) ) {
     438                        $avail_roles[$b_role]++;
     439                    } else {
     440                        $avail_roles[$b_role] = 1;
     441                    }
     442                }
     443            }
     444        }
     445
     446        $result['total_users'] = count( $users_of_blog );
     447        $result['avail_roles'] =& $avail_roles;
     448    }
     449
     450    return $result;
    343451}
    344452
     
    499607 * The finished user data is cached, but the cache is not used to fill in the
    500608 * user data for the given object. Once the function has been used, the cache
    501  * should be used to retrieve user data. The purpose seems then to be to ensure
    502  * that the data in the object is always fresh.
     609 * should be used to retrieve user data. The intention is if the current data
     610 * had been cached already, there would be no need to call this function.
    503611 *
    504612 * @access private
     
    509617 */
    510618function _fill_user( &$user ) {
     619    $metavalues = get_user_metavalues(array($user->ID));
     620    _fill_single_user($user, $metavalues[$user->ID]);
     621}
     622
     623/**
     624 * Perform the query to get the $metavalues array(s) needed by _fill_user and _fill_many_users
     625 *
     626 * @since 3.0.0
     627 * @param array $ids User ID numbers list.
     628 * @return array of arrays. The array is indexed by user_id, containing $metavalues object arrays.
     629 */
     630function get_user_metavalues($ids) {
    511631    global $wpdb;
    512632
     633    $clean = array_map('intval', $ids);
     634    if ( 0 == count($clean) )
     635        return $objects;
     636
     637    $list = implode(',', $clean);
     638
    513639    $show = $wpdb->hide_errors();
    514     $metavalues = $wpdb->get_results($wpdb->prepare("SELECT meta_key, meta_value FROM $wpdb->usermeta WHERE user_id = %d", $user->ID));
     640    $metavalues = $wpdb->get_results("SELECT user_id, meta_key, meta_value FROM $wpdb->usermeta WHERE user_id IN ($list)");
    515641    $wpdb->show_errors($show);
    516642
    517     if ( $metavalues ) {
    518         foreach ( (array) $metavalues as $meta ) {
    519             $value = maybe_unserialize($meta->meta_value);
    520             $user->{$meta->meta_key} = $value;
    521         }
     643    $objects = array();
     644    foreach($clean as $id) {
     645        $objects[$id] = array();
     646    }
     647    foreach($metavalues as $meta_object) {
     648        $objects[$meta_object->user_id][] = $meta_object;
     649    }
     650
     651    return $objects;
     652}
     653
     654/**
     655 * Unserialize user metadata, fill $user object, then cache everything.
     656 *
     657 * @since 3.0.0
     658 * @param object $user The User object.
     659 * @param array $metavalues An array of objects provided by get_user_metavalues()
     660 */
     661function _fill_single_user( &$user, &$metavalues ) {
     662    global $wpdb;
     663
     664    foreach ( $metavalues as $meta ) {
     665        $value = maybe_unserialize($meta->meta_value);
     666        $user->{$meta->meta_key} = $value;
    522667    }
    523668
     
    534679        $user->user_description = $user->description;
    535680
    536     wp_cache_add($user->ID, $user, 'users');
    537     wp_cache_add($user->user_login, $user->ID, 'userlogins');
    538     wp_cache_add($user->user_email, $user->ID, 'useremail');
    539     wp_cache_add($user->user_nicename, $user->ID, 'userslugs');
     681    update_user_caches($user);
     682}
     683
     684/**
     685 * Take an array of user objects, fill them with metas, and cache them.
     686 *
     687 * @since 3.0.0
     688 * @param array $users User objects
     689 * @param array $metas User metavalues objects
     690 */
     691function _fill_many_users( &$users ) {
     692    $ids = array();
     693    foreach($users as $user_object) {
     694        $ids[] = $user_object->ID;
     695    }
     696
     697    $metas = get_user_metavalues($ids);
     698
     699    foreach($users as $user_object) {
     700        if (isset($metas[$user_object->ID])) {
     701            _fill_single_user($user_object, $metas[$user_object->ID]);
     702        }
     703    }
    540704}
    541705
     
    657821
    658822/**
     823 * Update all user caches
     824 *
     825 * @since 3.0.0
     826 *
     827 * @param object $user User object to be cached
     828 */
     829function update_user_caches(&$user) {
     830    wp_cache_add($user->ID, $user, 'users');
     831    wp_cache_add($user->user_login, $user->ID, 'userlogins');
     832    wp_cache_add($user->user_email, $user->ID, 'useremail');
     833    wp_cache_add($user->user_nicename, $user->ID, 'userslugs');
     834}
     835
     836/**
    659837 * Clean all user caches
    660838 *
    661  * @since 3.0
     839 * @since 3.0.0
    662840 *
    663841 * @param int $id User ID
    664  * @return void
    665842 */
    666843function clean_user_cache($id) {
Note: See TracChangeset for help on using the changeset viewer.