Skip to:
Content
Pages
Categories
Search
Top
Bottom

Increase search member performance


  • elpix
    Participant

    @elpix

    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 🙂

Viewing 2 replies - 1 through 2 (of 2 total)

  • shanebp
    Moderator

    @shanebp

    Prior to BP 1.7, last_activity was stored usermeta.
    Now it is stored in the activity table, (although you may see it still in usermeta for legacy purposes).

    Moving it to the activity table resulted in much improved performance.
    You’ll probably see similar results.

    also – sometimes 2 queries are faster than one massive one with multiple joins, ands, ors.

    also 2 – Have you studied this?
    https://wordpress.org/plugins/bp-profile-search/
    It’s been around for several years and the query has become more efficient with each release.


    elpix
    Participant

    @elpix

    Thanks Shane,

    I’ve found a solution, here is my query :

    SELECT wp_bp_activity.user_id AS ID FROM wp_bp_activity WHERE wp_bp_activity.type = 'last_activity' AND wp_bp_activity.user_id IN(SELECT user_id FROM wp_bp_xprofile_data WHERE value LIKE '%cancer%' OR value LIKE '%neoplasms%' ) ORDER BY date_recorded DESC LIMIT 0 , 20

    This is much faster than the previous query 😉

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Increase search member performance’ is closed to new replies.
Skip to toolbar