Changeset 5940

Timestamp:

03/20/2012 09:16:58 PM (13 years ago)

Author:

boonebgorges

Message:

Provide index hints for queries in BP_Activity_Activity::get(). See #4045.

File:

• trunk/bp-activity/bp-activity-classes.php (modified) (3 diffs)

trunk/bp-activity/bp-activity-classes.php

@@ -107 +107 @@
         $select_sql = "SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name";
 
-        $from_sql = " FROM {$bp->activity->table_name} a LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
+        $from_sql = " FROM {$bp->activity->table_name} a USE INDEX (date_recorded) LEFT JOIN {$wpdb->users} u ON a.user_id = u.ID";
 
         // Where conditions
@@ -156 +156 @@
         $where_sql = 'WHERE ' . join( ' AND ', $where_conditions );
 
+        // Define the preferred order for indexes
+        $indexes = apply_filters( 'bp_activity_preferred_index_order', array( 'user_id', 'item_id', 'secondary_item_id', 'date_recorded', 'component', 'type', 'hide_sitewide', 'is_spam' ) );
+        
+        foreach( $indexes as $key => $index ) {
+            if ( false !== strpos( $where_sql, $index ) ) {
+                $the_index = $index;
+                break; // Take the first one we find
+            }
+        }
+
+        if ( !empty( $the_index ) ) {
+            $index_hint_sql = $wpdb->prepare( "USE INDEX ({$the_index})" ); 
+        } else {
+            $index_hint_sql = '';
+        }
+
         if ( !empty( $per_page ) && !empty( $page ) ) {
 
@@ -168 +184 @@
         }
 
-        $total_activities_sql = apply_filters( 'bp_activity_total_activities_sql', $wpdb->prepare( "SELECT count(a.id) FROM {$bp->activity->table_name} a {$where_sql} ORDER BY a.date_recorded {$sort}" ), $where_sql, $sort );
+        $total_activities_sql = apply_filters( 'bp_activity_total_activities_sql', $wpdb->prepare( "SELECT count(a.id) FROM {$bp->activity->table_name} a {$index_hint_sql} {$where_sql} ORDER BY a.date_recorded {$sort}" ), $where_sql, $sort );
 
         $total_activities = $wpdb->get_var( $total_activities_sql );