Update view count, most reliable way

Question

Hello again Stackoverflow!

I'm currently working on custom forumsoftware and one of the things you like to see on a forum is a viewcounter.

All the approaches for a viewcounter that I found would just select the topic from the database, retrieve the number from a "views" column, add one and update it.

But here's my thought: If, lets say 400, people at the exact same time open a topic, the MySQL database probably won´t count all views because it takes time for the queries to complete, and so the last person (of the 400) might overwrites the first persons (of the 400) view.

Ofcourse one could argue that on a normal site this is never going to happen, but if you have ~7 people opening that topic at the exact same second and the server is struggleing at that moment, you could have the same problem.

Is there any other good approach to count views?

EDIT

Woah, could the one who voted down specify why?

I ment by "Retrieving the number of views and adding one" that I would use SELECT to retrieve the number, add one using PHP (note the tags) and updating it using UPDATE. I had no idea of the other methods specified below, that's why I asked.

score 2 · Answer 1 · answered Jul 31 '13 at 11:23

2

If, lets say 400, people at the exact same time open a topic, the MySQL database apparently would count all the views because this is exactly what databases were invented for.

All the approaches for a viewcounter that you have found are wrong. To update a field you don't need to retrieve it, but just already update:

UPDATE forum SET views + 1 WHERE id = ?

answered Jul 31 '13 at 11:23

Your Common Sense

156,878
40
214
345

Now is my question, how? – Thew Jul 31 '13 at 11:24
Up date the views entry in the database before the page is displayed. `UPDATE \`views_table\` SET \`views\` = \`views\` + 1 WHERE \`post_id\` = '$post_id' LIMIT 1;` – Andy Gee Jul 31 '13 at 11:27

score 1 · Accepted Answer · edited May 23 '17 at 10:32

So something like that will work:

UPDATE tbl SET cnt = cnt+1 WHERE ...

UPDATE is guaranteed to be atomic. That means no one will be able to alter cnt between the time it is read and the time it is replaced. If you have several concurrent UPDATE for the same row (InnoDB) or table (MyISAM) they have to wait their turn to update the date.

See Is incrementing a field in MySQL atomic?
and http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html

Update view count, most reliable way

2 Answers2