MYSQL’s slow_query log shows multiple entries for the following query:
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 '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_custom' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_custom_fields' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR (`wp_usermeta`.`meta_key` = 'wp_s2member_notes' AND `wp_usermeta`.`meta_value` LIKE '%[USER_LOGIN]%') OR `user_login` LIKE '%[USER_LOGIN]%' OR `user_nicename` LIKE '%[USER_LOGIN]%' OR `user_email` LIKE '%[USER_LOGIN]%' OR `user_url` LIKE '%[USER_LOGIN]%' OR `display_name` LIKE '%[USER_LOGIN]%') AND `wp_users`.`ID` IN(SELECT DISTINCT(`user_id`) FROM `wp_usermeta` WHERE `meta_key` = 'wp_capabilities') ORDER BY user_login ASC LIMIT 20
I already added a compound index on user_id & meta_key as suggested in another thread but this did not seem to help. Any ideas on how to optimize for better response times?