Page 1 of 1

storing and calculating website stats in db

Posted: Mon Sep 03, 2007 2:43 am
by mechamecha
Hello,
I'm trying to implement a mechanism that tracks the number of times a user's photo is viewed. I would like to then display this number for the users to see. This the design I've come up with:

I'll have a table that looks like this:

CREATE TABLE `photo_views` (
`user_id` bigint(20) NOT NULL
`photo_id` bigint(20) NOT NULL,
`count` bigint(20) NOT NULL,
PRIMARY KEY (`user_id`)
)

Each time a photo link is clicked, the site will store the user_id that the photo belongs to, the photo's id, and a 1 for the count.

In the background, I would have an agent scheduled to periodically run(once a day is sufficient). This agent will traverse the photo_views table and calculate the sum of all 'count' values for each user_id and photo_id. Then it would delete all rows that it used to calculate the sum and write a single row back into the table w/ the calculated sum in the 'count' column. It would also save the sum in another table, let' say its called 'count_photo_views'.

Between the summing and deletion sql operations that the agent performs, I would need to lock the table from modification(I'm assuming this is not too hard to do....).

Every time a user wants to display the number of times a photo is viewed, the site would query the 'count_photo_views' table to get this value.

With this design, I hope to keep the photo_views table as compact as possible and the sql calculation that performs the summation as quick as possible. What do you guys think? Is there a better alternative?

Thanks!

Posted: Mon Sep 03, 2007 4:10 am
by onion2k
I don't understand why you're trying to make a complicated system simply to count how many views a photo has had. Just put a count column in the photo table, and increment it whenever a photo is viewed... eg

Code: Select all

UPDATE `photo` SET `photo`.`counter` = `photo`.`counter` + 1 WHERE `photo`.`photo_id` = 1
The only reason to put the count in another table would be if you want to know when the photo was downloaded, or who downloaded it.

Posted: Mon Sep 03, 2007 12:33 pm
by mechamecha
onion2k,
THanks for the reply!

I like your solution. It's much simpler. However I also want to save the accumulated total views for each day. This way I could easily compute and display the new number of views a photo has recieved. I think I could use your solution in conjunction w/ an agent that runs daily and stores each row from the photo_views table into another table.

What do you think?

Posted: Mon Sep 03, 2007 4:47 pm
by CoderGoblin
Simple solution would be to have the previous solution with a date. A lot depends on the amount of traffic. I would be tempted to store just a photo_id and timestamp. This could be used to actually provide more details such as times when photos were viewed but could lead to a large table if there is high traffic. The other solution would be to create a photo_id/counter/date if it does not exist or update the counter based on date. You could do this by trying an update first. If mysql_affected_rows returns 0, create it.