About: Joel Peterson

Yup, now it works great – sorry for all that!

I am having the same issue. Although I have 8,000 members, and it is taking down our whole server when we do a search. It locks the wp_usermeta table so that no other query can run. The only way to bring the server back is to kill the query.

We have a dedicated server with 32 G of RAM, so it definitely isn’t the server.

Here is the query that locks:

SELECT SQL_CALC_FOUND_ROWS wp_users.ID  
FROM `wp_users` 
INNER JOIN `wp_usermeta` ON `wp_users`.`ID` = `wp_usermeta`.`user_id`  
WHERE '1' = '1' AND ( 
(`wp_usermeta`.`meta_key` = 'wp_s2member_subscr_id' AND `wp_usermeta`.`meta_value` LIKE '%SEARCH-STRING%') OR 		(`wp_usermeta`.`meta_key` = 'wp_s2member_custom' AND `wp_usermeta`.`meta_value` LIKE '%SEARCH-STRING%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_custom_fields' AND `wp_usermeta`.`meta_value` LIKE '%SEARCH-STRING%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_notes' AND `wp_usermeta`.`meta_value` LIKE '%SEARCH-STRING%') OR (`wp_usermeta`.`meta_key` LIKE '%_name' AND `wp_usermeta`.`meta_value` LIKE '%SEARCH-STRING%') OR 
`user_login` LIKE '%SEARCH-STRING%' OR 
`user_nicename` LIKE '%SEARCH-STRING%' OR 
`user_email` LIKE '%SEARCH-STRING%' OR 
`user_url` LIKE '%SEARCH-STRING%' OR 
`display_name` LIKE '%SEARCH-STRING0%'
) AND `wp_users`.`ID` IN(
	SELECT DISTINCT(`user_id`) FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities'
) 
ORDER BY user_login ASC LIMIT 20 

Please advise.