Skip to:

Slow Queries

  • yesbutmaybeno


    My site has a good 15,000+ users, of which about 100+ online in BP area at any given moment (so no caching etc)

    Constantly tweaking things for performance. Finally checked out the MySQL slow query log and it’s a lot of stuff. But a solid 80% of it area queries like this

    # Query_time: 3.036380  Lock_time: 0.000062  Rows_sent: 0  Rows_examined: 772130
    SET timestamp=1607682328;
    SELECT DISTINCT  FROM wp_bp_activity a  WHERE
                    a.content LIKE '%@bobtho<%'
                    a.hide_sitewide = 0
    AND a.is_spam = 0 AND a.type NOT IN ('last_activity') ORDER BY a.date_recorded DESC, DESC LIMIT 0, 21;

    Query time is 3.2 seconds, some are upwards of 5+

    I think it’s coming from here and particularly when a user presses their “My Mentions” tab, thus its loading all of their activities that include their “@ username” text

    My CPU load is *always* high, I’ve upgraded my server earlier in the year:3.4 GHz 4 vCores and 16 GB RAM so it’s not bad

    Basically, I’m wondering if anything can be done about this? Since my slow-query log is filled with these types of queries (specifically it seems to be this “@ username” search) – can any optimizations be done, either in BP or something I can hack onto it?

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

  • shanebp


    Yes, LIKE is slow.
    Mentions are handled differently as you can see; a css class is added to the content field.
    So perhaps the id of the mentioned user could be added to the item_id or secondary_item_id field.
    Then the query could just look for matching integers.
    I suggest you open an enhancement ticket – and reference this thread.

    Angel Sanchez


    Hi @yesbutmaybeno,

    Were you able to fix your problem? I find myself in the same situation.

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.
Skip to toolbar