Background:
We are collecting data and pushing it to MySQL at various intervals. Sometimes it may be every minute, other times every 15 minutes, and all ranges in between. This data is timestamped using the UNIX epoch timestamp (if needed I could convert this to a MySQL timestamp).
When viewing the data in just one day, it is okay to show all rows, but we run into a problem with load times when we go past say a week... (at 1440 rows per day a week contains 10,080 rows - on top of that if we're looking at multiple columns say 3, we're now at 30,000+ pieces of data). This is really bogging down our graph displays.
Question:
I'm wondering if there is way to say find the average of rows between X period, so if we're looking at an entire year, I can average and sum the minutely data to hourly or daily data. I know there is an AVG function, but the way I understand it is that that would average all given rows, I want to be able to average all rows between X and Y have that as a returned row from the query and then continue to average between the next X and Y. I hope that makes sense.
I guess one way to attack it is multiple queries, but I'd like to be able to do this in a single query if it's possible. If we're looking at daily averages over a year it would be nice to not have to submit 365 separate queries to the database - not to mention the math/logistics in figuring out X date starts at Y timestamp and ends at Z timestamp.
The idea is to make this query code/platform independent so whether we're accessing data via PHP or Java we don't have to write separate logic on each platform to decide how the queries are going to look.
Any ideas on attacking this problem would be welcomed - Thanks a bunch!