Tracking the views of a given row

I have a site where the users can view quite a large number of posts. Every time this is done I run a query similar to UPDATE table SET views=views+1 WHERE id = ?. However, there are a number of disadvantages to this approach:

• There is no way of tracking when the pageviews occur – they are simply incremented.
• Updating the table that often will, as far as I understand it, clear the MySQL cache of the row, thus making the next SELECT of that row slower.

Therefore I consider employing an approach where I create a table, say:
object_views { object_id, year, month, day, views }, so that each object has one row pr. day in this table. I would then periodically update the views column in the objects table so that I wouldn’t have to do expensive joins all the time.

This is the simplest solution I can think of, and it seems that it is also the one with the least performance impact. Do you agree?

(The site is build on PHP 5.2, Symfony 1.4 and Doctrine 1.2 in case you wonder)

Edit:
The purpose is not web analytics – I know how to do that, and that is already in place. There are two purposes:

• Allow the user to see how many times a given object has been shown, for example today or yesterday.
• Allow the moderators of the site to see simple view statistics without going into Google Analytics, Omniture or whatever solution. Furthermore, the results in the backend must be realtime, a feature which GA cannot offer at this time. I do not wish to use the Analytics API to retrieve the usage data (not realtime, GA requires JavaScript).

Answer

Quote : Updating the table that often will, as far as I understand it, clear the MySQL cache of the row, thus making the next SELECT of that row slower.

There is much more than this. This is database killer. I suggest u make table like this : object_views { object_id, timestamp} This way you can aggregate on object_id (count() function). So every time someone view the page you will INSERT record in the table. Once in a while you must clean the old records in the table. UPDATE statement is EVIL 🙂 On most platforms it will basically mark the row as deleted and insert a new one thus making the table fragmented. Not to mention locking issues .

Hope that helps