title: PDO/MySQL memory consumption with large result set
body: <p>I'm having a strange time dealing with selecting from a table with about 30,000 rows.</p>
  
  <p>It seems my script is using an outrageous amount of memory for what is a simple, forward only walk over a query result.</p>
  
  <p>Please note that this example is a somewhat contrived, absolute bare minimum example which bears very little resemblance to the real code and it cannot be replaced with a simple database aggregation. It is intended to illustrate the point that each row does not need to be retained on each iteration.</p>
  
  <pre><code>&lt;?php
  $pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
      PDO::ATTR_ERRMODE=&gt;PDO::ERRMODE_EXCEPTION,
  ));
  $stmt = $pdo-&gt;prepare('SELECT * FROM round');
  $stmt-&gt;execute();
  
  function do_stuff($row) {}
  
  $c = 0;
  while ($row = $stmt-&gt;fetch()) {
      // do something with the object that doesn't involve keeping 
      // it around and can't be done in SQL
      do_stuff($row);
      $row = null;
      ++$c;
  }
  
  var_dump($c);
  var_dump(memory_get_usage());
  var_dump(memory_get_peak_usage());
  </code></pre>
  
  <p>This outputs:</p>
  
  <pre><code>int(39508)
  int(43005064)
  int(43018120)
  </code></pre>
  
  <p>I don't understand why 40 meg of memory is used when hardly any data needs to be held at any one time. I have already worked out I can reduce the memory by a factor of about 6 by replacing "SELECT *" with "SELECT home, away", however I consider even this usage to be insanely high and the table is only going to get bigger.</p>
  
  <p>Is there a setting I'm missing, or is there some limitation in PDO that I should be aware of? I'm happy to get rid of PDO in favour of mysqli if it can not support this, so if that's my only option, how would I perform this using mysqli instead?</p>