返回SQL查询的每第二行

Question

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.

Answer 1

<?
$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...