[buddypress-trac] [BuddyPress] #4060: slow queries in bp-core-classes

[buddypress-trac at lists.automattic.com]

#4060: slow queries in bp-core-classes
--------------------------+--------------------
 Reporter:  shanebp       |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  1.6
Component:  Members       |     Version:  1.2.9
 Severity:  normal        |  Resolution:
 Keywords:                |
--------------------------+--------------------

Comment (by shanebp):

 boonegorges - seems like we're having a bit of an apples/oranges
 discussion.

 For us, the issue isn't joining on wp_bp_activity - it's the left join on
 wp_metausers in BP_Core_User -> get_users();

 Selects on wp_bp_activity never shows up in our slow-log.
 But get_users with the left join does frequently - and other simple
 queries start stacking up behind it.

 ( I've looked at #4045. We only have ~250k rows in that table. )

 Adding only as many rows as there are members is exactly the problem with
 using wp_usermeta to store 'last_activity' time-stamp.

 A table only holding only last_activity [id, user_id, last_activity]
  I think would be faster, very indexable and last_activity could be a
 date-time column instead of longtext - as used in wp_usermeta. And it
 would only have the same number of rows as wp_users.

 I'm not sure I have the skills to do the benchmarking you mentioned - but
 it would be great to see it.

 I cringe at the thought of adding a last_activity table. But we don't have
 much choice.
 We switched from MyISAM to InnoDB, doubled the size of our db server,
 optimized our searches, etc. The result is that our page loads have stayed
 steady - but that's all, so it's not enough.

 We have ~25k very active users; the average online number is 400.

 Anyhow, thanks for your input.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4060#comment:6>
BuddyPress <http://buddypress.org/>
BuddyPress