Extremly slow mysql queries for buddypress activity stream

Archives rtMedia
#1

Hi,

after the latest updates I have serious problems with the response times of my activity stream.

There is a waiting time from some seconds up to more then a minute.

Is there a problem (known?) with rtmedia, rtmedia privacy settings?

Or do you think the problems do not come from rtMedia?

I've added some debug information for you.

# Time: 130906 23:52:59  
# User@Host: XXXXXXXXXXXX] @ localhost []  
# Query_time: 24.278179  Lock_time: 0.000071 Rows_sent: 20  Rows_examined: 48081226  
use XXXXXXXXXXXXXXX;  
SET timestamp=1378504379;  
SELECT distinct 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_rt_rtm_media m ON a.id = m.activity_id WHERE a.is_spam = 0 AND a.type IN ( 'bbp_reply_create' ) AND a.hide_sitewide = 0 AND a.type != 'activity_comment' 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.privacy is NULL OR CONVERT(m.privacy,SIGNED INTEGER) <= 0)OR ((m.privacy=20) OR (a.user_id=1 AND CONVERT(m.privacy, UNSIGNED INTEGER) >= 40) OR (CONVERT(m.privacy,UNSIGNED INTEGER)=40 AND a.user_id IN ('34345'))) ) ) ORDER BY a.date_recorded DESC LIMIT 0, 20;

I think this has to do with rtMedia because the problem only seems to occur if rtmedia items are included in the activity steam filter.

Debug-Information for rtMedia  
PHP     5.4.4-14+deb7u4  
MYSQL   5.5.31  
WordPress   3.6  
BuddyPress  1.8.1  
rtMedia     3.1  
OS  Linux  
Imagick     Not Installed  
GD  2.0  
[php.ini] post_max_size     8M  
[php.ini] upload_max_filesize   6M  
[php.ini] memory_limit  128M

Thanks for your hints..

Florian

#2

Just add index on activity_id column will solve this issue.

ALTER TABLE `wp_rt_rtm_media`   
ADD INDEX `activity_id` (`activity_id` ASC) ;
#3

Wow. This was really a quick reply! Thanks! Seemed to help. Would not it be good to include this index automatically into every rtMedia installation with the next version?

another thing (absolutely off topic, but maybe you can help): what's the name of the plugin that displayes a live preview of the post I am actually writing here? I'm searching for something like this but I could not find one that worked...

#4

Yes we already added in code and it will available in next release. you can download it from github.

We used WP-Markdown Version 1.4 | By Stephen Harris

#5

thanks a lot for you work and your answer!

#6