Most efficient way to list 'popular' items on very high load website

[eluser]James Spibey[/eluser]
Hi,

I'm looking for some feedback on the most efficient way to determine what the most popular articles/items are for a given time period. The way this is currently implemented on our website is causing us issues because I think we are getting into a position where our database is locking whilst trying to retrieve the list of popular items.

For simplicity's sake, let's say we have a table where we record pagehits with the appropriate article id. We are currently using the following query to get the 5 most popular articles for the last 7 days

Code:

SELECT `id`, `title`, `body`
FROM (`articles`)
WHERE articles.active = 1
ORDER BY(SELECT COUNT(*) FROM pageviews pv WHERE pv.page_type = 2 AND pv.page_id = articles.id AND from_unixtime(pv.datecreated) > date_add(curdate(), interval -7 day))
LIMIT 5

This query is used on our homepage but is cached so it only runs every 60 seconds. However, under certain circumstances, it can lock the database entirely which I believe is because the pageviews table is receiving a large amount of inserts whilst the query is trying to run.

Is there a better way of doing this kind of thing?

Thanks

James