PHP - Best practice for storing page view in database

Question

May I know what's the best practice for storing page views in database?

Is it costly to update the pageview value every time the page is loaded?

And is there any possible errors/threats while updating the pageview and in the same time, someone else is modifying the same row's data?

For e.g.

Table - Item ID Name Description PageView

I understand it might not be a very big issue, it can apply to some other data that updates very often, would like to know what's the best approach of doing it. The possible scenario that came into my mind was, if there's a lot of pages is running in the simultaneously. Will there be any performance issue?

Pardon my English, and many thanks in advance.

Answer 1

The best approach is to write a new line into the table for every view, rather than to keep a rolling tally which could lead to locking problems on a large system. For additional performance, you can add rows using INSERT DELAYED syntax. This will allow the DB handle to return immediately and your script won't wait for the insert to complete. Documentation here:-

http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html

It might also be worth looking at the Archive storage engine which is aimed specifically at this type of logging. You can only INSERT and SELECT data but performance is aimed at rapid writing.

http://dev.mysql.com/doc/refman/5.1/en/archive-storage-engine.html

To view pageview information you simply query the number of rows for any given page. A major advantage of this approach is that by logging a timestamp with every page view, you can analyse data by time of day, days of week etc. and see what shape your traffic is.