Changeset 11886

Timestamp:

03/05/2018 08:44:16 AM (9 years ago)

Author:

djpaul

Message:

Members, search: split user matching into seperate query.

Removing the subquery decreases the amount of rows MySQL needs to look at in the bp_activity table.
In the future, if we or WordPress add a user cache incrementor, we could cache this query.

Fixes #7442

Props brandonliles, r-a-y, boonebgorges

Location:

trunk/src

Files:

• bp-core/classes/class-bp-user-query.php (modified) (1 diff)
• bp-xprofile/bp-xprofile-functions.php (modified) (1 diff)

trunk/src/bp-core/classes/class-bp-user-query.php

@@ -414 +414 @@
                         }
 
-                        $sql['where']['search'] = $wpdb->prepare(
-                                "u.{$this->uid_name} IN ( SELECT ID FROM {$wpdb->users} WHERE ( user_login LIKE %s OR user_login LIKE %s OR user_nicename LIKE %s OR user_nicename LIKE %s ) )",
+                        $matched_user_ids = $wpdb->get_col( $wpdb->prepare(
+                                "SELECT ID FROM {$wpdb->users} WHERE ( user_login LIKE %s OR user_login LIKE %s OR user_nicename LIKE %s OR user_nicename LIKE %s )",
                                 $search_terms_nospace,
                                 $search_terms_space,
                                 $search_terms_nospace,
                                 $search_terms_space
-                        );
+                        ) );
+
+                        $match_in_clause = empty( $matched_user_ids) ? 'NULL' : implode( ',', $matched_user_ids );
+                        $sql['where']['search'] = "u.{$this->uid_name} IN ({$match_in_clause})";
                 }
 

trunk/src/bp-xprofile/bp-xprofile-functions.php

@@ -785 +785 @@
         // Combine the core search (against wp_users) into a single OR clause
         // with the xprofile_data search.
-        $search_xprofile = $wpdb->prepare(
-                "u.{$query->uid_name} IN ( SELECT user_id FROM {$bp->profile->table_name_data} WHERE value LIKE %s OR value LIKE %s )",
+        $matched_user_ids = $wpdb->get_col( $wpdb->prepare(
+                "SELECT user_id FROM {$bp->profile->table_name_data} WHERE value LIKE %s OR value LIKE %s",
                 $search_terms_nospace,
                 $search_terms_space
-        );
-
-        $search_core     = $sql['where']['search'];
-        $search_combined = "( {$search_xprofile} OR {$search_core} )";
-        $sql['where']['search'] = $search_combined;
+        ) );
+
+        if ( ! empty( $matched_user_ids ) ) {
+                $search_core     = $sql['where']['search'];
+                $search_combined = " ( u.{$query->uid_name} IN (" . implode(',', $matched_user_ids) . ") OR {$search_core} )";
+                $sql['where']['search'] = $search_combined;
+        }
 
         return $sql;