Re: [PHP] PDO buffered query problem

> Stewart Duncan wrote:
>> Hi there,
>>
>> I'm having some serious problems with the PHP Data Object functions. 
>> I'm trying to loop through a sizeable result set (~60k rows, ~1gig) 
>> using a buffered query to avoid fetching the whole set.
>>
>> No matter what I do, the script just hangs on the PDO::query() - it 
>> seems the query is running unbuffered (why else would the change in 
>> result set size 'fix' the issue?). Here is my code to reproduce the 
>> problem:
>>
>> <?php
>> $Database = new PDO(
>>     'mysql:host=localhost;port=3306;dbname=mydatabase',
>>     'root',
>>     '',
>>     array(
>>         PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
>>         PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
>>     )
>> );
>
> Don't you want it the other way around? You want it unbuffered so it 
> doesn't get retrieved in mysql, send the whole result set to php then 
> you can use it.
>
> You want to start using it immediately - so make it unbuffered.
>

Either way if the result set is going to be large your MySQL's memory or 
PHP's memory may exceed. So if you use either you may need to fine-tune 
PHP by increasing the per process memory (memory_limit in php.ini) or MySQL.

In case you use unbuffered queries you cannot use transactions as far as 
I can recall.

-- 
Thodoris