I have a log table. It logs visitor data (time of visit, browser, window size ...).
I want to create reports. I use a query like this (MySQL):
SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp)
The problem is
DATE(timestamp). I am using a function on this field, so the index won't work. But the table is huge. Index are very important. But how else can I create hourly, daily, yearly, monthly reports?
I tried to find out how others are doing it. For example Piwik (an open source Google Analytics like script). But I don't really get it.