Group count database query needs optimization

[arpit.tambi.in]

We have like more than 80K buddypress groups. The database queries in function get_group_ids in bp-groups-classes.php will take forever to run and eat away all CPU.

We have a dedicated server, 8 cores, 24GB RAM and overall website is fast enough. Following is EXPLAIN output from MySql.

EXPLAIN SELECT COUNT(DISTINCT m.group_id) FROM wp_bp_groups_members m, wp_bp_groups g WHERE g.status != 'hidden' AND m.user_id = 1 AND m.is_confirmed = 1 AND m.is_banned = 0:

*** row 1 ***
          table:  g
           type:  range
  possible_keys:  status
            key:  status
        key_len:  32
            ref:  NULL
           rows:  38825
          Extra:  Using where; Using index
*** row 2 ***
          table:  m
           type:  ALL
  possible_keys:  user_id,is_confirmed
            key:  NULL
        key_len:  NULL
            ref:  NULL
           rows:  82461
          Extra:  Using where; Using join buffer

Clearly indexes are not being used for group members table. Let me know if any other details are needed.

[DJPaul]

I get slightly different EXPLAIN results for that query, but I'm only testing on a small database at the moment. This probably won't get addressed in BP 1.7 unless someone contributes a patch and some benchmarks, but am copying in Boone as he's done some SQL work for other parts of 1.7, and might be able to spot any easy wins

[boonebgorges]

It's worth testing whether simply switching the join order would fix it, since presumably the group member part of the query will yield far fewer results than the groups portion. I can run some benchmarks at some point, but this will definitely be fixed faster if someone else can do it.

[sooskriszta]

@arpit.tambi.in How is 2.0 working out for you? Seen any improvements?
​http://bpdevel.wordpress.com/2014/04/02/one-of-the-primary-focuses/

[DJPaul]

We're closing this ticket because it has not received any contribution or comments for at least two years. We have decided that it is better to close tickets that are good ideas, which have not gotten (or are unlikely to get) contributions, rather than keep things open indefinitely. This will help us share a more realistic roadmap for BuddyPress with you.

Everyone very much appreciates the time and effort that you spent sharing your idea with us. On behalf of the entire BuddyPress team, thank you.

If you feel strongly that this enhancement should still be added to BuddyPress, and you are able to contribute effort towards it, we encourage you to re-open the ticket, or start a discussion about it in our Slack channel. Please consider that time has proven that good ideas without contributions do not get built.

For more information, see ​https://bpdevel.wordpress.com/2018/01/21/our-awaiting-contributions-milestone-contains/
or find us on Slack, in the #buddypress channel: ​https://make.wordpress.org/chat/