php/mysql - logging users activities & huge database load

Question

Assuming we have to log all the users activties of a community, i guess that in brief time our database will become very huge; so my question is:

is this anyway an acceptable compromise (to have a huge DB table) in order to offer this kind of service? Or we can do this in more efficent way?

EDIT: the kind of activity to be logged is a "classic" social-networking activity-log whre people can look what others are doing or have done and viceversa, so it will track for example when user edit profile, post something, login, logout etc...

EDIT 2: my table is already optimized in order to store only id's

log_activity_table(
id int
user int 
ip varchar
event varchar #event-name
time varchar
callbacks text #some-info-from-the-triggered-event
)

Answer 1

Im actually working on a similar system so Im interested in the answers you get.

For my project, having a full historical accounting was not important so we chose to keep the table fairly lean much like what youre doing. Our tables look something like this:

CREATE TABLE `activity_log_entry` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event` varchar(50) NOT NULL,
  `subject` text,
  `publisher_id` bigint(20) NOT NULL,
  `created_at` datetime NOT NULL,
  `expires_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_log_entry_action_idx` (`action`),
  KEY `event_log_entry_publisher_id_idx` (`publisher_id`),
  CONSTRAINT `event_log_entry_publisher_id_user_id` 
    FOREIGN KEY (`publisher_id`)  
    REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We decided that we dont want to store history forever so we will have a cron job that kills history after a certain time period. We have both created_at and expired_at columns simply out of convenience. When an event is logged these columns are updated automatically by the model and we use a simple strftime('%F %T', strtotime($expr)) where $expr is a string like '+30 days' we pull from configuration.

Our subject column is similar to your callback one. We also chose not to directly relate the subject of the activity to other tables because there is a possibility that not all event subjects will have a table, additionally its not even important to hold this relationship because the only thing we do with this event log is display activity feed messages. We store a serialized value object of data pertinent to the event for use in predetermined message templates. We also directly encode what the event pertained to (ie. profile, comment, status, etc..).

Our events (aka activities.) are simple strings like 'update','create', etc.. These are used in some queries and of course to help determine which message to display to a user.

We are still in the early stages so this may change quite a bit (possibly based on comments and answers to this question) but given our requirements it seemed like a good approach.