Skip to:
Content

BuddyPress.org

Opened 9 years ago

Closed 4 years ago

#4609 closed enhancement (maybelater)

Group count database query needs optimization

Reported by: arpit.tambi.in Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Groups Keywords: trac-tidy-2018
Cc: boonebgorges

Description

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.

Change History (5)

#1 @DJPaul
9 years ago

  • Cc boonebgorges added
  • Milestone changed from Awaiting Review to Future Release

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

#2 @boonebgorges
9 years ago

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.

#3 @sooskriszta
8 years ago

@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/

#4 @DJPaul
4 years ago

  • Keywords trac-tidy-2018 added

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/

#5 @DJPaul
4 years ago

  • Milestone Awaiting Contributions deleted
  • Resolution set to maybelater
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.