title: mysql/php - How to implementing a unique view system?
body: <p><strong>Introduction</strong></p>
  
  <p>I am wondering what is the best way to implement a unique views system... I think I know how, so if I could explain how I think to do it and you point out any mistakes or improvements.</p>
  
  <p>obviously I will have to store a log table containing a video id and something which (relatively) uniquely identifies the user. At first I considered a combination of header request and IP but decided to keep it simple and use just IP. Also that way a user can not increase the views of their video by using a different browser.</p>
  
  <p><strong>This is how I would think to do it:</strong></p>
  
  <ol>
  <li><p>When a user visits I do a SELECT
  similar to this:</p>
  
  <p><code>SELECT 1 FROM tbl_log WHERE IP =
  $usersip AND video_id = $video_id</code></p>
  
  <ul>
  <li>if there is no result then I must
  insert a record </li>
  </ul>
  
  <p><code>INSERT into tbl_log (IP,video_id)
  VALUES ($usersip, $video_id)</code></p>
  
  <ul>
  <li>and increase the views by 1</li>
  </ul>
  
  <p><code>SELECT views FROM tbl_video WHERE
  video_id = $video_id</code></p>
  
  <p><code>UPDATE tbl_video SET views =
  $result['views'] + 1 WHERE video_id
  = $video_id</code></p></li>
  </ol>
  
  <p><strong>Questions</strong></p>
  
  <ul>
  <li><p>I guess I do not want to have<br>
  millions of log records slowing down 
  my site so should I run a cron job to
  empty the log table once a day?</p></li>
  <li><p>Should I make the views<br>
  transactional? (I guess a slightly<br>
  depreciated view count is less<br>
  important than a slow site because of
  row locks)</p></li>
  <li><p>Is there a way to reduce the load on 
  the mysql server.... I fear if every 
  view requires an increased view count
  and an IP log that it will be pretty 
  expensive. I have seen that youtube<br>
  and the like do not update the views 
  instantly... do they cache the<br>
  updates some how and then run them at
  once? if so how?</p></li>
  <li><p>How efficient is my system? Can you
  think of any improvements?</p></li>
  </ul>