Recently Active widget doesn't scale
|Reported by:||mpvanwinkle77||Owned by:|
Hello, I don't have a solution for this yet, but I wanted to add it to the mix. The Recently Active (and Who's Online too I think) widgets don't scale well. I have a site with 98,000+ registered users and this query used by the widget was just killing the site:
SELECT DISTINCT u.ID as id, u.user_registered, u.user_nicename, u.user_login, u.display_name, u.user_email , um.meta_value as last_activity FROM wp_users u LEFT JOIN wp_usermeta um ON um.user_id = u.ID WHERE u.spam = 0 AND u.deleted = 0 AND u.user_status = 0 AND um.meta_key = 'last_activity' ORDER BY um.meta_value DESC LIMIT 0, 18;
Clearly the scale of our community is a big factor. But we have an entire server dedicated solely to MySQL and three for httpd ... and even with these resources we were getting crushed.
I'm trying to think of ways to scale this better. I'm considering creating a field in the options table to store IDs of logged in users and then running those IDs through get_userdata() which should at least be getting cached by W3 Total Cache. This approach would come with a little more PHP overhead but hopefully spare my database server the burden of the direct user query.
In general I think some modifications to bp_core_get_users() ( i.e. BP_Core_User ) could solve the problem. Perhaps adding caching logic? Or perhaps querying just the wp_usermeta table for user_id's and then populating each one with get_userdata() ?
So anyway, just wanted to start the discussion and get feedback.