Changeset 11886

Timestamp:

03/05/2018 08:44:16 AM (7 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;