storing and calculating website stats in db

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mechamecha
Forum Commoner
Posts: 32
Joined: Thu May 31, 2007 8:49 pm

storing and calculating website stats in db

Post 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!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
mechamecha
Forum Commoner
Posts: 32
Joined: Thu May 31, 2007 8:49 pm

Post 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?
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
Post Reply