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 usingUPDATE
. 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 simpleSELECT 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:
http://www.mysqlperformanceblog.com/2007/07/01/implementing-efficient-counters-with-mysql/
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.
Best Answer
If scalability is more important to you than absolute accuracy of the figures then you could cache the view count in your application for a short time rather than hitting the database on every page view - eg, only update the database once every 100 views.
If your application crashes between database updates then obviously you'll lose some of your data, but if you can tolerate a certain amount of inaccuracy then this might be a useful approach.