The RTMediaPrivacy code is inefficient


#1

Hi,

Even when the Privacy mode is disabled, RTMediaPrivacy class is instantiated and through the bp_activity_get_user_join_filter filter, every user join query becomes very slow on a large site.

Consider that the following original query:SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name FROM wp_bp_activity a LEFT JOIN wp_users u ON a.user_id = u.ID WHERE a.is_spam = 0 AND a.type IN ( 'pjmotd' ) AND a.hide_sitewide = 0 ORDER BY a.date_recorded DESC LIMIT 0, 1; Time: 1 row in set (0.01 sec)

is modified into:

SELECT distinct a.*, u.user_email, u.user_nicename, u.user_login, u.display_name FROM wp_bp_activity a LEFT JOIN wp_users u ON a.user_id = u.ID LEFT JOIN wp_bp_activity_meta m ON a.id = m.activity_id WHERE a.is_spam = 0 AND a.type IN ( 'pjmotd' ) AND a.hide_sitewide = 0 AND (NOT EXISTS (SELECT m.activity_id FROM wp_bp_activity_meta m WHERE m.meta_key='rtmedia_privacy' AND m.activity_id=a.id) OR (m.meta_key='rtmedia_privacy' AND (CONVERT(m.meta_value,SIGNED INTEGER) <= 0)OR ((m.meta_value=20) OR (a.user_id=1 AND CONVERT(m.meta_value, UNSIGNED INTEGER) >= 40) OR (CONVERT(m.meta_value,UNSIGNED INTEGER)=40 AND a.user_id IN ('622','504','509','413','478','329','98','396','375','429','370','321','352','337','331','36','302','183','89','280','263','236','77','31','28','189','175','25','159','62','19','4','122','7','15','96','20','60','52','41','12','44','35','33','26','9','22'))) ) ) ORDER BY a.date_recorded DESC LIMIT 0, 1; 1 row in set (0.55 sec)

All of it just adds to very slow site because this query is used all over BuddyPress.

At the very least, I think this feature should not be loaded if Privacy is disabled to allow larger sites to continue to function until a better way is found.


#2

Thanks for pointing this out. We will be resolving this in the next release. From next time onwards, you could also send a pull request on GitHub, so that you could be added to the contributors list.


#3