Skip to:
Content

BuddyPress.org


Ignore:
Timestamp:
06/09/2014 06:33:05 PM (8 years ago)
Author:
boonebgorges
Message:

Disable COUNT queries by default in bp_activity_get() stack

The COUNT query performed to get the total activity count has proven to be a
performance bottleneck on large sites. Moreover, the way that activity items
are displayed on the front end (using the Load More link) means that the total
count is not actually used by BuddyPress in most cases. To reduce query
overhead, we introduce a 'count_total' parameter to the bp_activity_get()
stack, and set it to false by default.

For backward compatibility, a few additional changes are introduced:

  • In the activity-loop.php template, the <noscript> pagination markup is removed. In its place, the Load More link is refactored so that it loads the next available page of activity items.
  • The mechanism used to determine whether there are more activity items to show (bp_activity_has_more_items()) has been refined; when no COUNT query takes place, we query for the $per_page value + 1 to infer whether more items are available.

Fixes #5629

Props boonebgorges, r-a-y

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/src/bp-activity/bp-activity-classes.php

    r8251 r8491  
    268268     *     @type bool $update_meta_cache Whether to pre-fetch metadata for
    269269     *           queried activity items. Default: true.
     270     *     @type string|bool $count_total If true, an additional DB query
     271     *           is run to count the total activity items for the query.
     272     *           Default: false.
    270273     * }
    271274     * @return array The array returned has two keys:
     
    312315            'spam'              => 'ham_only', // Spam status
    313316            'update_meta_cache' => true,
     317            'count_total'       => false,
    314318        );
    315319        $r = wp_parse_args( $args, $defaults );
     
    412416        $per_page = absint( $per_page );
    413417
     418        $retval = array(
     419            'activities'     => null,
     420            'total'          => null,
     421            'has_more_items' => null,
     422        );
     423
    414424        // Filter and return true to use the legacy query structure (not recommended)
    415425        if ( apply_filters( 'bp_use_legacy_activity_query', false, __METHOD__, $r ) ) {
     
    432442
    433443            if ( ! empty( $per_page ) && ! empty( $page ) ) {
    434                 $activity_ids_sql .= $wpdb->prepare( " LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page );
     444                // We query for $per_page + 1 items in order to
     445                // populate the has_more_items flag
     446                $activity_ids_sql .= $wpdb->prepare( " LIMIT %d, %d", absint( ( $page - 1 ) * $per_page ), $per_page + 1 );
    435447            }
    436448
     
    438450
    439451            $activity_ids = $wpdb->get_col( $activity_ids_sql );
    440             $activities   = self::get_activity_data( $activity_ids );
    441         }
    442 
    443         $total_activities_sql = apply_filters( 'bp_activity_total_activities_sql', "SELECT count(DISTINCT a.id) FROM {$bp->activity->table_name} a {$join_sql} {$where_sql}", $where_sql, $sort );
    444         $total_activities     = $wpdb->get_var( $total_activities_sql );
     452
     453            $retval['has_more_items'] = ! empty( $per_page ) && count( $activity_ids ) > $per_page;
     454
     455            // If we've fetched more than the $per_page value, we
     456            // can discard the extra now
     457            if ( ! empty( $per_page ) && count( $activity_ids ) === $per_page + 1 ) {
     458                array_pop( $activity_ids );
     459            }
     460
     461            $activities = self::get_activity_data( $activity_ids );
     462        }
    445463
    446464        // Get the fullnames of users so we don't have to query in the loop
     
    466484        $activities = BP_Activity_Activity::generate_action_strings( $activities );
    467485
     486        $retval['activities'] = $activities;
     487
    468488        // If $max is set, only return up to the max results
    469         if ( !empty( $max ) ) {
    470             if ( (int) $total_activities > (int) $max )
    471                 $total_activities = $max;
    472         }
    473 
    474         return array( 'activities' => $activities, 'total' => (int) $total_activities );
     489        if ( ! empty( $r['count_total'] ) ) {
     490
     491            $total_activities_sql = apply_filters( 'bp_activity_total_activities_sql', "SELECT count(DISTINCT a.id) FROM {$bp->activity->table_name} a {$join_sql} {$where_sql}", $where_sql, $sort );
     492            $total_activities     = $wpdb->get_var( $total_activities_sql );
     493
     494            if ( !empty( $max ) ) {
     495                if ( (int) $total_activities > (int) $max )
     496                    $total_activities = $max;
     497            }
     498
     499            $retval['total'] = $total_activities;
     500        }
     501
     502        return $retval;
    475503    }
    476504
Note: See TracChangeset for help on using the changeset viewer.