I want to start counting the numbers of times a webpage is viewed and hence need some kind of simple counter. What is the best scalable method of doing this?
Suppose I have a table Frobs where each row corresponds to a page – some obvious options are:
Have an unsigned int NumViews field
in the Frobs table which gets
updated upon each view using
UPDATE. Simple but not so good at scaling as I understand it.
Frobs SET NumViews = NumViews + 1
Have a separate table FrobViews
where a new row is inserted for each view. To display the
number of views, you then need to do a simple
SELECT COUNT(*) AS NumViews FROM FrobViews WHERE FrobId = '%d' GROUP BY FrobId. This doesn't involve any updates so can avoid table locking in MyISAM tables – however, the read performance will suffer if you want to display the number of views on each page.
How do you do it?
There's some good advice here:
but I'd like to hear the views of the SO community.
I'm using InnoDb at the moment, but am interested in answers for both InnoDb and MyISAM.