BuddyPress.org

Hi There !

I’m trying to code my own “Search member” plugin.
I have a big problem with performance, here is my query:

SELECT DISTINCT (
wp_users.ID
)
FROM wp_users
JOIN wp_bp_xprofile_data ON wp_users.ID = wp_bp_xprofile_data.user_ID
JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_ID
WHERE 1 =1
AND (
wp_bp_xprofile_data.value LIKE  '%cancer%'
OR display_name LIKE  '%cancer%'
OR wp_bp_xprofile_data.value LIKE  '%neoplasms%'
OR display_name LIKE  '%neoplasms%'
)
AND wp_users.id
IN (

SELECT user_id
FROM wp_bp_xprofile_data
WHERE field_id =6
AND (
value LIKE  '%Australia%'
OR value LIKE  '%France%'
)
)
AND meta_key =  'last_activity'
ORDER BY meta_value DESC 

It seems that the ORDER BY meta_value DESC takes a long times to execute.
The “meta value” column is in “longtext”, I’ve tried an index but there is no changes.

Do you know what I can do to increase the speed ?

Thanks a lot 🙂