title: Export large CSV file
body: <p>I have a mysql table where each record can have unlimited custom fields (EAV model, doesn't matter this) and each field can have unlimited options and each option can have unlimited values.<br>
  Right now i am trying to built a export tool that will export all these custom fields with their values, that is: name => value pairs for each field. That's not the important part, it's here just to highlight that we're talking about a lot of mysql queries for a single record and that the size of the export will be pretty large.  </p>
  
  <p>For each row from my main table i must do around 100 separate sql queries to get the fields, fields options and field options values. These queries are pretty fast because they all use the right indexes, but still we're talking about 100 queries for a single record and i expect to have around 50k records in my main table just to start with.  </p>
  
  <p>Right now, what i do is:  </p>
  
  <pre><code>set_time_limit(0);
  ini_set('memory_limit', '1G');
  ini_set("auto_detect_line_endings", true);
  
  $count = $export-&gt;count();
  $date = date('Y-m-d-H-i-s');
  $fileName = CHtml::encode($export-&gt;name) .'-'. $date . '.csv';
  
  $processAtOnce = 100;
  $rounds = round($count / $processAtOnce);
  
  header("Content-disposition: attachment; filename={$fileName}");
  header("Content-Type: text/csv");
  
  $headerSet = false;
  for ($i = 0; $i &lt; $rounds; ++$i) {
  
      $limit = $processAtOnce;
      $offset = $i * $processAtOnce;
      $rows = $export-&gt;find($limit, $offset);
  
      if (empty($rows)) {
          continue;
      }
  
      $outStream = fopen('php://output', 'w');
  
      if (!$headerSet) {
          fputcsv($outStream, array_keys($rows[0]), ',', '"');    
          $headerSet = true;
      }
  
      foreach ($rows as $row) {
          fputcsv($outStream, array_values($row), ',', '"');
      }
  
      echo fgets($outStream);
  
      fclose($outStream);
  }
  </code></pre>
  
  <p>Basically i count all the records and i "paginate" them for export, then run through the pages to avoin loading too many sql results at once.<br>
  I am wondering if this is a valid approach? Any thoughts?   </p>
  
  <p>My alternative would be to count all the records, split them into "pages" and for each page do an ajax request(recursive function called after the previous request has been made successfully). When doing the ajax request, process maybe 1k records at once(these 1k would also be splitted like in the above example, run internally 10 times with 100 results for example), write them into a temporary directory(like part-1.csv, part-2.csv) and at the end when all the records are processed, create an archive from the folder containing all the csv parts and force the browser to download it then remove it from the server(window.location.href from within the last ajax call).<br>
  Is this a good alternative to the above?  </p>
  
  <p>Please note, my goal is to limit the amount of memory usage that's why i think the second approach would help me more.  </p>
  
  <p>Please let me know what you think.<br>
  Thanks.</p>