I have two tables, news and news_views. Every time an article is viewed, the news id, IP address and date is recorded in news_views.

I'm using a query with a subquery to fetch the most viewed titles from news, by getting the total count of views in the last 24 hours for each one.

It works fine except that it takes between 5-10 seconds to run, presumably because there's hundreds of thousands of rows in news_views and it has to go through the entire table before it can finish. The query is as follows, is there any way at all it can be improved?

SELECT n.title
,      nv.views
FROM   news n
LEFT
JOIN   (
       SELECT news_id
       ,      count( DISTINCT ip ) AS views
       FROM   news_views
       WHERE  datetime >= SUBDATE(now(), INTERVAL 24 HOUR)
       GROUP
       BY     news_id
       ) AS nv
ON     nv.news_id = n.id
ORDER
BY     views DESC
LIMIT  15