How to track views on an item a la YouTube?

Question

I would like to be able to track views of an item in a way that I can see what items were the most viewed yesterday, last week or any given period. How can I do that using either PHP or Rails? Is there a plugin for this in Rails, or is there a simple way of doing this using PHP/memcached?

Answer 1

You could have a simple SQL table with the video id, the date and an integer counter, incremented for each view.

To filter multiple views from an IP address, use a table containing video id and ip address. Truncate it each 24 hours, and increment the counter only if the ip address is not already in this table.

Answer 2

For each view, save some data into a SQL table.

• Unique ID
• The item ID
• Date (unixtime for example)
• User's IP

Then you can COUNT(id) WHERE id = number AND date > last week

SELECT COUNT(id) FROM view_table WHERE id = 1 AND date > 1241136000

Answer 3

As Rémy says, you can make this simple and have a database table tracking individual page views, although this can incur a bit of write load on views, which might be undesirable.

A compromise could be to write a row for each view to an intermediate, in-memory table, and periodically (perhaps via a cron job, or every N views) update the aggregate count in the main table. If your read load is really high, then you could consider storing that temporary counter in memcached; as you'll spot, the less often you write to the disk-backed tables, the less fault tolerant your counter becomes, although in the case of a simple popularity counter, that's probably not critical data.

Once you've got your page view count, you can do whatever statistical analysis you fancy.