I contacted our host for support, which is hostgator, and this was there response
Can anyone advise?
________________________________
From the system monitoring tool it would appear that MySQL is the source of the slowdowns and that the activity is brief. In the slow queries log there was only one query listed and it examined almost 880,000,00 rows to generate a result. You might want to consider some database optimization such as an index or taking a new approach to this query:
# Time: 130731 15:25:58
# User@Host: xxxxxxxxxxxxxxxxxx
xxxxxxx] @ localhost []
# Query_time: 205.773256 Lock_time: 0.000254 Rows_sent: 0 Rows_examined: 87993742
use xxxxxxxx;
SET timestamp=1375305958;
SELECT userId,COUNT(id) as count FROM ( SELECT action.`id`,cactivity.userId FROM ow_newsfeed_action action
INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId
INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId
INNER JOIN ow_newsfeed_action_feed action_feed ON activity.id=action_feed.activityId
LEFT JOIN ow_newsfeed_follow follow ON action_feed.feedId = follow.feedId AND action_feed.feedType = follow.feedType
LEFT JOIN ow_base_user_suspend AS us ON ( cactivity.userId = us.userId )
LEFT JOIN ow_base_user_disapprove AS ud ON ( cactivity.userId = ud.userId )
WHERE activity.status='active' AND activity.timeStamp<1375305752 AND (
( activity.visibility & 2 ) )
AND `us`.`userId` IS NULL AND `ud`.`userId` IS NULL
UNION
SELECT action.`id`,cactivity.userId FROM ow_newsfeed_action action
INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId
INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId
LEFT JOIN ow_base_user_suspend AS us ON ( cactivity.userId = us.userId )
LEFT JOIN ow_base_user_disapprove AS ud ON ( cactivity.userId = ud.userId )
WHERE activity.status='active' AND activity.timeStamp<'1375305752' AND (
( activity.visibility & 4 ) )
AND `us`.`userId` IS NULL AND `ud`.`userId` IS NULL
UNION
SELECT action.`id`,cactivity.userId FROM ow_newsfeed_action action
INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId
INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId
INNER JOIN ow_newsfeed_action_feed action_feed ON activity.id=action_feed.activityId
LEFT JOIN ow_base_user_suspend AS us ON ( cactivity.userId = us.userId )
LEFT JOIN ow_base_user_disapprove AS ud ON ( cactivity.userId = ud.userId )
WHERE activity.status='active' AND activity.timeStamp<'1375305752'
AND ( ( action_feed.feedType="user" AND activity.visibility & 8 ) )
AND `us`.`userId` IS NULL AND `ud`.`userId` IS NULL
UNION
SELECT action.`id`,cactivity.userId FROM ow_newsfeed_action action
INNER JOIN ow_newsfeed_activity activity ON action.id = activity.actionId
INNER JOIN `ow_newsfeed_action_set` cactivity ON action.id = cactivity.actionId
INNER JOIN ow_newsfeed_activity subscribe ON activity.actionId=subscribe.actionId and subscribe.activityType='subscribe'
LEFT JOIN ow_base_user_suspend AS us ON ( cactivity.userId = us.userId )
LEFT JOIN ow_base_user_disapprove AS ud ON ( cactivity.userId = ud.userId )
WHERE activity.status='active' AND activity.timeStamp<'1375305752'
AND `us`.`userId` IS NULL AND `ud`.`userId` IS NULL) a GROUP BY userId
ORDER BY count DESC
LIMIT 0, 10;
