ernest1b - 11:25 am on Oct 18, 2010 (gmt 0)
I found a better way. I need to create 3 more fields:
pageId | dayViews | monthViews | yearViews | date
This would means 3 queries to get day, month and year views:
$yearAgo=time() - (365 * 24 * 60 * 60);
$monthAgo=time() - (30 * 24 * 60 * 60);
SELECT dayViews, monthViews, yearViews FROM pageViews ORDER BY date DESC
SELECT dayViews FROM pageViews WHERE date<$yearAgo ORDER BY date DESC
SELECT dayViews FROM pageViews WHERE date<$monthAgo ORDER BY date DESC
In this way I get all data from the most recent date. Than I need to subtract from this date pageViews from the first day of 30 and 365 days ago.
and finally 1 extra query to create or update row with 1 more pageViews
INSERT INTO pageViews (pageId, dayViews, monthViews, yearViews, date)VALUES($pageId, 1, $monthViews, $yearViews)
ON duplicate KEY UPDATE dayViews=dayViews+1,monthViews=monthViews+1,yearViews=yearViews+1,;
where primary key is date.
Is 4 queries in each pageview for statistic in this way too wasting?