Mysql – What’s the best way to implement a counter field in MySQL

MySQL

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:

  1. Have an unsigned int NumViews field
    in the Frobs table which gets
    updated upon each view using UPDATE
    Frobs SET NumViews = NumViews + 1
    . Simple but not so good at scaling as I understand it.

  2. 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:
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.