What's the best way to implement a counter field in MySQL

Question

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.

Answer 1

I would take your second approach and aggregate the data into the table from your first solution on a regular base. On this way you get the advandages of both solutions. To be clearer: On every hit you insert a row into a table (lets name it hit_counters). This table got only one field (the pageid). Every x seconds you run a script (via a cronjob) which aggregates the data from the hit_counters table and put it into a second table (lets name it 'hits'. There you got two fields: the pageid and the total hits.

Im not sure but imho does innodb not help you very much for solution 1 if you get many hits on the same page: Innodb locks the row while updating so all other updates to this row will be delayed.

Depending on whats your program written in you could also batch the updates together by counting in your application and updating the database only every x seconds. This would only work if you use a programing language where you got persistent storage (like Java Servlets but not PHP)

Answer 2

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.

Answer 3

What I do, and it may not apply to your scenario, is in the stored procedure that prepares/returns the data that is displayed on the page, I make the table counter update at the same time it returns the data - that way, there is only one call to the server that both gets the data, and updates the counter in the same call.

If you are not using SP's,(or if there is no database data on your page) this option may not be available to you, but if you are, its something to consider.