I have a web page where users upload&watch videos. Last week I asked what is the best way to track video views so that I could display the most viewed videos this week (videos from all dates).

Now I need some help optimizing a query with which I get the videos from the database. The relevant tables are this:

video (~239371 rows)
VID(int), UID(int), title(varchar), status(enum), type(varchar), is_duplicate(enum), is_adult(enum), channel_id(tinyint)

signup (~115440 rows)
UID(int), username(varchar)

videos_views (~359202 rows after 6 days of collecting data, so this table will grow rapidly)
videos_id(int), views_date(date), num_of_views(int)

The table video holds the videos, signup hodls users and videos_views holds data about video views (each video can have one row per day in that table).

I have this query that does the trick, but takes ~10s to execute, and I imagine this will only get worse over time as the videos_views table grows in size.

SELECT
 v.VID, 
 v.title, 
 v.vkey, 
 v.duration, 
 v.addtime, 
 v.UID, 
 v.viewnumber, 
 v.com_num, 
 v.rate, 
 v.THB, 
 s.username,
 SUM(vvt.num_of_views) AS tmp_num
FROM
 video v
  LEFT JOIN videos_views vvt ON v.VID = vvt.videos_id
  LEFT JOIN signup s on v.UID = s.UID
WHERE
 v.status = 'Converted'
 AND v.type = 'public'
 AND v.is_duplicate = '0'
 AND v.is_adult = '0'
 AND v.channel_id <> 10
 AND vvt.views_date >= '2001-05-11'
GROUP BY
 vvt.videos_id
ORDER BY
 tmp_num DESC
LIMIT
 8

And here is a screenshot of the EXPLAIN result:

So, how can I optimize this?