I am working with an app which uses phpActiveRecord and mySQL to pull in data from a sensor network and plot it onto a number of flot.js graphs on the client.
There are several timeframes the user can chose between to affect the range of data the graphs display. 2hrs, 24hrs, 3 days and 1 week.
The sensors post to the database every 60 seconds, so when plotting the graphs, the query pulls in all rows between now and DATE_SUB(CUR_DATE - INTERVAL ? DAY) where ? is either 1, 3 or 7 etc.
However this results in a massive number of rows being returned (60,000 + for the full week!) and is causing huge delays and server errors.
I know I can just massively increase the max memory available for queries in the php.ini file, but this is hardly a good solution, and doesn't solve the issue of speed.
My question is, is there a way I can easily select only every second or third row from the required date range depending on the length of the interval the user wishes to view?
In C or Java I would do something like a modulo select to return alternate rows but I cannot think of a way to do this in the current framework.
Any ideas would be appreciated. Thanks.
<?
$row = 1;
WHILE QUERY {
if ($row % 2 == 0) {
echo "Yourstuff";
} else {
//Nothing
}
$row++;
}
?>
This should help you to think about a solution..maybe not the perfect one for you, but i hope it helps... For every third use $row%3 and so on...