first_row, last_row

[eluser]Unknown[/eluser]
See code below...

Code:

function query_events_older_than_timeperiod($value,$period)
   {
    $this->load->database('snortdb',TRUE);
    $sql = "SELECT cid,sid,timestamp FROM event WHERE timestamp < NOW() - INTERVAL " . $value . " " . $period ." ORDER BY timestamp ASC";
    $query = $this->db->query($sql);

    $first_ev = $query->first_row('array');
    $last_ev = $query->last_row('array');

    $results = array('query' => $query,
                     'fe' => $first_ev['timestamp']);

    return $results;
   }

Everytime this function is executed I get the following error:

Fatal error: Allowed memory size of 18874368 bytes exhausted (tried to allocate 39 bytes) in /var/www/html/idscontrol/system/database/drivers/mysql/mysql_result.php on line 153

I have increased the memory allowed to scripts to 18M, but I do not think I should have to keep increasing it, that is not a solution.

If I remove the first_row() and last_row() calls, I get no errors. This query is returning ~45K rows.

Can anyone think of why asking for the first and last rows would cause this?

[eluser]sophistry[/eluser]
welcome to CI funkshun!

I don't have an answer per se, but you might get faster response by changing your approach.

Make two queries: both with LIMIT 1, one with ORDER BY ASC and one DESC.

just a thought... might not work with your requirements.

cheers.

[eluser]Unknown[/eluser]
I hadn't thought of that, and it would work, but I do think I should need to do that since these functions are suppose to accomplish it. I may do that in the meantime as a workaround. Thx.

[eluser]sophistry[/eluser]
i'd call it optimization rather than workaround... maybe the "bytes exhausted" error is trying to tell you something ;-)

EDIT: I just looked at the CI $query->first_row() method and it currently sports an (overlooked?) resource-greedy while loop through the ENTIRE RESULT ARRAY just to get the first result as an array. so, the optimization i suggested actually avoids this CI performance bottleneck. this should be posted in the bug report section of the forum...

funkshun, would you like to do the honors?