title: Mysql: Count rows for period of time
body: <p>I have a table videos and I need to create top videos for it.</p>
  
  <p>For the start i was using simple counter per video row and updated it on each user view like that:</p>
  
  <pre><code>UPDATE videos SET video_id=video_id+1 WHERE video_id=?
  </code></pre>
  
  <p>After that we have decided to create three different tops and add some unification to the results per ip, to make tops more accurate:
  - top of the day
  - top of the week
  - top of the month</p>
  
  <p>I have added new table to count each user view as a separate row.</p>
  
  <pre><code>INSERT INTO video_views(video_id,date,ip) VALUES(video_id,current date,user ip)
  </code></pre>
  
  <p>Everything went ok until video_views table became very big.</p>
  
  <p>Here is query for top:</p>
  
  <pre><code>SELECT COUNT(vv.`view_id`) as counter,v.* FROM video v
  LEFT JOIN video_views vv ON vv.`video_id`=v.`video_id`
  WHERE vv.`date`&gt;1320120130 /* It's the time = current time minus week or day */
  GROUP BY v.`video_id`
  ORDER BY counter DESC
  LIMIT 50
  </code></pre>
  
  <p>Problem starts at this part:     </p>
  
  <pre><code>ORDER BY counter DESC
  </code></pre>
  
  <p>Query takes about 20 seconds to finish. Without order it's as usually 16-32ms. Here is what EXPLAIN telling:</p>
  
  <pre><code>id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
  1,SIMPLE,vv,range,date,video_id,date,5,NULL,149552,Using where; Using temporary; Using filesort
  1,SIMPLE,v,eq_ref,PRIMARY,PRIMARY,4,movieslv_website.vv.video_id,1,
  </code></pre>
  
  <p>As i understand it's because of "Using temporary" , mysql creates temporary table to fit the results and sort them afterwards?</p>
  
  <p>What solutions can be applied to help in this situation?</p>
  
  <p>Thanks :)</p>