Wordpress - Searching all concatenated (extra) metadata fields requires bigger group_concat_max_len setting

Sometimes in Wordpress you have to extend a search to extra meta fields, but under the hood it's a little more complicated. One way is to concatenate all user fields into 1 long string to efficiently search. 

But if you're not careful, and depending on the number of additional fields added this will fail.

SET SESSION group_concat_max_len = 1048576;
SELECT 
  DISTINCT t1.ID AS ID, 
  CONCAT(
    t1.user_nicename, ' ', t1.user_email, 
    ' ', t1.user_url, ' ', t1.display_name
  ) as WPUsersFields 
FROM 
  wp_users as t1

Here's how to extend the mySQL group_concat_max_len size so that Concat can search all the bytes in the concatenated fields, in this case WPUsersFields. 

1048576 represents 1Mb of searchable text

This is useful if you have a huge user profile that you have to search. Below $wpdb will execute 2 mySQL statements at the same time.

Here's the code

1 2 3 4 5 6 7 8 9

<?php global $wpdb; $wpdb->query('SET SESSION group_concat_max_len = 1048576;'); //https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html $SQL = "SELECT DISTINCT t1.ID AS ID, CONCAT( t1.user_nicename, ' ', t1.user_email, ' ', t1.user_url, ' ', t1.display_name ) as WPUsersFields FROM wp_users as t1"; $result = $wpdb->get_results($SQL); ?>