title: How to export millions of rows from MySQL to CSV via PHP without exhausting memory?
body: <p>So I have this table:</p>
  <pre><code>mysql&gt; DESCRIBE table;
  +-------+------------------+------+-----+---------+----------------+
  | Field | Type             | Null | Key | Default | Extra          |
  +-------+------------------+------+-----+---------+----------------+
  | id    | int(15) unsigned | NO   | PRI | NULL    | auto_increment |
  | unid  | char(9)          | NO   | UNI | NULL    |                |
  | rs    | varchar(255)     | NO   |     | NULL    |                |
  +-------+------------------+------+-----+---------+----------------+
  3 rows in set (0.00 sec)
  </code></pre>
  <p>Which contains millions of rows:</p>
  <pre><code>mysql&gt; SELECT COUNT(1) FROM table;
  +----------+
  | COUNT(1) |
  +----------+
  |  9435361 |
  +----------+
  1 row in set (0.00 sec)
  </code></pre>
  <p>I'm willing to export all rows in a <code>.csv</code> file (I'm using <code>Symfony2.6</code>). This file is meant to be stored on the server (not downloaded) and later on, read by PHP.</p>
  <p><strong>1st attempt</strong></p>
  <p>I tried to make a huge request to select all at once (<a href="http://obtao.com/blog/2013/12/export-data-to-a-csv-file-with-symfony/" rel="noreferrer">as per this blog post</a>) but this has, despite the use of <code>-&gt;iterate()</code>, led to <code>Allowed memory size of 1073741824 bytes exhausted</code> after having run for ~9s.</p>
  <pre><code>    ini_set('memory_limit', '1024M');
      ini_set('max_execution_time', -1);
  
      $results = $em
          -&gt;getRepository('MyBundle:Entity')
          -&gt;createQueryBuilder('e')
          -&gt;getQuery()
          -&gt;iterate();
      $handle = fopen('/path/to/csv/file/', 'w');
      while (false !== ($row = $results-&gt;next())) {
          fputcsv($handle, $row[0]-&gt;toArray());
          $em-&gt;detach($row[0]);
      }
      fclose($handle);
  </code></pre>
  <p><strong>2nd attempt</strong></p>
  <p>I retrieved the total of rows and then, did a loop to make the same number of queries to retrieve rows one by one. But after having written ~260K rows into the <code>.csv</code> file, PHP runs out of memory and throws the same error as above : <code>Allowed memory size of 1073741824 bytes exhausted</code>.</p>
  <pre><code>    ini_set('memory_limit', '1024M');
      ini_set('max_execution_time', -1);
  
      $total = (int) $em
          -&gt;getRepository('MyBundle:Entity')
          -&gt;countAll();
      $csv = '/path/to/csv/file';
      $handle = fopen($csv, 'w');
      for($i = 1; $i &lt; $total; $i++)
      {
          $entity = $em-&gt;getRepository('MyBundle:Entity')-&gt;findOneById($i);
          fputcsv($handle, $entity-&gt;toArray());
          $em-&gt;detach($entity);
      }
      fclose($handle);
  </code></pre>
  <p><strong>3rd attempt</strong></p>
  <p>I have thought of the use of the <code>exec()</code> function to run the MySQL command line that would export the table. However, my manager seems not to like this option.</p>
  <hr />
  <p>So am I making a fool of myself thinking that dumping ~9.5M of rows using PHP into a <code>.csv</code> file is even possible? Are there any other way I'm not yet aware of?</p>
  <p>Thanks for your help on this one.</p>