Active Record and large result sets

[eluser]BlueCamel[/eluser]
I'm dealing with large result sets from my SQL queries and using active record. I don't want to return the entire result set into memory. Instead, I want to step through the results row by row (think cursor).

So, I think AR by default returns a total count for MySQL results. Because of this isn't the entire result set stored in memory? If I disable the total count, will I be able to step through the results without storing the entire set in memory?

Did this make any sense?

[eluser]xwero[/eluser]
Use the count_total_results method to get the row count and use limit for returning data.

[eluser]adamp1[/eluser]
Yer only way to do that is to only return one row at a time with the search IE LIMIT 1. Otherwise its not possible. But then that will be expensive on database calls if your getting alot of rows. I would advice pulling it out of the database in chunks which you are happy to store in memory.

[eluser]Negligence[/eluser]
No you won't, the entire record set is stored in memory whenever you call one of the fetch methods. The only way around this is to step through the rows one by one outside of the database class. You'd have to pass the query result back to the model/library, then loop through it there.

I think you can access the variable $this->db->result_id to accomplish this.

[eluser]BlueCamel[/eluser]
So this is a limit of the current AR class? Looking at the PHP docs for mysqli, it appears that several functions loop through the results without pulling the entire result set into a php var.

http://us.php.net/manual/en/function.mys...result.php

Note: The mysqli_use_result() function does not transfer the entire result set from the database and hence cannot be used functions such as mysqli_data_seek() to move to a particular row within the set. To use this functionality, the result set must be stored using mysqli_store_result(). One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.