0

For stackoverflow.com there is a stat for how many views for a question.

How is it tracked? Registered and unregistered user can view any question.

What is the database relation schema for that counting?

Should i just increment the column value for a specific question in database table?

Or should i add a new record/row when an user views that question, then to display the count echo the result of sum(thatcolumn) of that table?

Dan
  • 9,391
  • 5
  • 41
  • 73
cola
  • 12,198
  • 36
  • 105
  • 165
  • Please, we cant tell you stackoverflow's structure. thats secret =). Be more specific, your questions are too general – safarov Mar 29 '12 at 20:23

2 Answers2

0

If all you are interested in is the number of times a question is viewed, I would add a counter to the specific question record. Having an entry in another table for each and every question view could get out of hand rather easily.

Summarizing those records just to get a view count every time could also lead to performance problems on a busy system.

So, to explain further, I suggest you add an integer column to your question table. Every time a question is viewed, you simply increment the counter. Keep it simple. Keep it fast.

datagod
  • 1,031
  • 1
  • 13
  • 21
  • Can you explain more? I could not understand your answer. Should i increment the row for each view? – cola Mar 29 '12 at 20:30
0

There are numerous approaches to this. there's the two you mentioned but there are others that will depending on your situation suit you better.

if you have a lot of hits to your site like stackoverflow does, incrementing the counter on a database table when one person views the question can be quite expensive (depending how their db is set up i'm not sure).

another approach which I've use before is to store the views in a cache engine like memcache/xcache/eaccelerator (each of these have their own merits) and have a cron that will run hourly/nightly/etc. that will clean up the views and insert into a database table that is referenced by the unique id in SO.com's case the question id, and reset the counters in the cache.

If you want total reliability a noSQL engine like mongoDB would be excellent at quick and efficient key value data storage.

ncremins
  • 9,140
  • 2
  • 25
  • 24
  • Why is it expensive to increment that row's value with that page/question/post? Which method is better between that two method i mentioned? Does the cache engine increment the count? What's the mechanishm of cache engine? – cola Mar 29 '12 at 20:38
  • not saying it's expensive (can be expensive) but without knowing your system it can be. how well could your system handle 10,000 updates a second? see where I'm coming from? the cache engine doesn't increment the count, you read the cache, increment it by 1, then store that in the cache for the next person to increment with a view. I can't say from which of the two is better as we don't know your system. Incrementing the column is not a bad approach if you only have like say 500-1000 views an hour lets say. – ncremins Mar 29 '12 at 20:47
  • What about the second way? Add a new record/row for each view, then for display the count do sum(thatcolumn) or count(*) for that table. – cola Mar 29 '12 at 20:50
  • Since your in the planning staging you really need to think about how it will scale. You may only have 50-100 visits a day now, but what happens 6 months down the line when those visits increase by a factor of 20 and it's not by day but by hour. My own personal opinion here but I prefer not to have stats in relational databases at all especially views and read stats as they constantly change. – ncremins Mar 29 '12 at 20:55
  • Suppose there will be more than 20000 views for page. Then what would you use/prefer? – cola Mar 29 '12 at 21:07
  • if i can only pick from the two you suggested, then the second one. 20000 inserts are least expensive than 20000 updates where id=n – ncremins Mar 29 '12 at 21:11
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/9480/discussion-between-prime-and-creminsn) – cola Mar 29 '12 at 21:16
  • When would you use memcache/xcache/eaccelerator ? – cola Mar 29 '12 at 21:32
  • What method do you use now? Why don't you use memcache/xcache/eaccelerator now? – cola Mar 29 '12 at 23:01