Gdn_Activity Table

Costello80

The VPS provider told me that mysql executes very slow query like:

SET timestamp=1382439712;
select count(a.ActivityID) as ActivityCount
from GDN_Activity a
join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID
where a.CommentActivityID is null
and (a.ActivityUserID = '4436'
or a.RegardingUserID = '4436')
and t.Public = '1';

First, I've added an index on GDN_Activity.ActivityType then my questions are:

What are these activities exactly? Why mysql executes over 50 of these queries in a second? Can i delete old records (years ago)? Can i disable them? (many questions....)
Keep in mind that this forum has over of 4 millions of impressions a month.

Thanks in advance for any tips.

x00

yes is is a good idea to purge activities. You wan to keep wall messages

in crontab

00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2" 

Replace username and pass. You can see this deletes older then 2 months.

Activity: mindless piffle inspired by facebook/twitter mentality.

Costello80

Thanks!

x00

it is worth testing the query on a backup. Remember to specify forumdb.

x00

-