title: What is the best way to increase view counter column of a row in each select?
body: <p>Often with portals like news sites, I wonder whether it is good practice or not to update view counter field of a table while selecting the row. Lets say I have a News table with id, title, details, publishDate and viewCounter. Is it good to perform following query on each request of news detail page? How would this mixing of select and update together for each request hurt performance?</p>
  
  <pre><code>select * from News where id=120;
  update News
  set viewCounter=viewCounter+1
  where id=120;
  </code></pre>
  
  <p>Could there be any difference in performance if I put view tracker data in another table, say table ViewsCount with columns id, newsID,viewCount? In this case, I would execute following code:</p>
  
  <pre><code>select * from News where id=120;
  update ViewsCount
  set viewCount=viewCount+1
  where newsID=120;
  </code></pre>
  
  <p>I would see one more option where I would track browser request data for each request, and later aggregate the rows for each news id. With this design, I would run two queries for each request: select and insert, like following:</p>
  
  <pre><code>select * from News where id=120;
  insert into NewsView(newsID,browser,ipAddress,operatingSystem,col1,col2)
  values(120,'Netscape','202.xx.xx.xx','Windows',col1Value,col2Value)
  </code></pre>
  
  <p>But with this I have seen that in short span of time I would get lots of rows and database size also increases significantly with heavy traffic portals. This would definitely slow down aggregate queries.</p>
  
  <p>What are the alternatives I could use? Or is it ok to go with page view trackers like Google Analytics? I would welcome suggestions based on best practices you have been following in the similar context.</p>