Hi,

I have a table with a few million records, (a stats table).
Every time a page is visited, (page view, not unique), I increase that
page count.

For speed, the data is stored as INT, the page is given a unique ID.
I also sort the data in 24 hours chunks and the data is stored for 3
months.

So the data is stored in the form:
DAY 1
PAGE 1
COUNT 5000
PAGE 2
COUNT 1232
...
DAY 2
PAGE 1
COUNT 45454
PAGE 2
COUNT 12123
...
...
DAY 90
PAGE 1
COUNT 45454
PAGE 2
COUNT 12123
...

So each row represent a day, a page id and an visit count.

I want to retrieve the top 10 page views for the last 3 months every 24
hours for a report.
The problem is that such a report takes a very long time to return,
(20-40 secs).

What I do is...
SELECT
SUM( visit_pageview ) AS visit_pageview, page_id
FROM
bhost_stats
GROUP BY page_id
ORDER BY visit_pageview DESC
LIMIT 10

Can you think of a more efficient way to retrieve the data?
Or will I have to run a cron job on the server every 24 hours?

What do you think I should do to get the report?

Many thanks

FFMG


--

'webmaster forum' (http://www.httppoint.com) | 'Free Blogs'
(http://www.journalhome.com/) | 'webmaster Directory'
(http://www.webhostshunter.com/)
'Recreation Vehicle insurance'
(http://www.insurance-owl.com/other/car_rec.php) | 'Free URL
redirection service' (http://urlkick.com/)
------------------------------------------------------------------------
FFMG's Profile: http://www.httppoint.com/member.php?userid=580
View this thread: http://www.httppoint.com/showthread.php?t=19892

Message Posted via the webmaster forum http://www.httppoint.com, (Ad revenue sharing).