Greets. I am developing an online learning app and need to track user stats. I can do it in one of two ways and I'm not sure which way is better/faster. The first way is the most obvious way. I simply have a single "stats" table that stores the following info in separate columns:
stats_id (autoincrement)
session_id (to link all stats entries of the same session)
login (uniquely identifies user)
subscriptionid (uniquely identifies the course)
time
ip
formaction
page_id
thread_id
post_id
test_id
answers
Every time a page is loaded, an entry is created in the stats table. Depending on which part of my app they are using, different columns may be filled or blank and I can eaily reconstruct what they were doing based on the info. The only disadvantage I can see to this method is that it will result in a very large stats table, probably with millions of rows, and I'm afraid this will become very slow over time. Just in a couple of days of testing, the stats table already has about 600 rows and that's just a single user testing the system.
The other way I could save the stats is to serialize this info and store it in a single field of a single row with the user's other subscription info...I could just add a field called "sessiondata" or something and keep the data with the user's subscription. I would set up a stats array with the same keys as the above mentioned db columns. Then when I wanted to save my stats I could say:
$serialized_stats_array[$session_id][] = serialize($stats_array);
then just save the serialized stats array back to the same DB field each time. All of a given user's stats would reside in a single row of the DB in the "sessiondata" field, but that would mean that on every page load I would need to:
retrieve the serialized stats array for the specific user
unserialize the stats array
loop through the array to find the current session's stats
update the array or create a new entry if the current session's stats aren't found
reserialize the data
save the data back to the db
This sounds like too much to do on every page load and the serialization/deserialization overhead seems like it would grow too quickly to perform well.
Can anyone else offer some insight about the best way do handle my stats logging? I'm sort of hoping someone will tell me that my first method is the way to go and that I won't have to worry about performance issues with it. Any thoughts?