Can this MySQL subquery be optimised?
- by Dan
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
