title: Getting large data from MySQL database in lumps (like PhpStorm does it)
body: <p>In my database, I have huge tables with a couple of billions of results and the problems are that my PHP code is not capable of getting it right. I'm waiting for hours of results and almost always get an error like 'served moved away' or 'maximum limit of passing data is xx'. </p>
  
  <p>Tables have indexed columns. Queries are simplified to the minimum. I have tried some test in PHP but all of them failed.</p>
  
  <pre><code>// approach #1 with PHP
  $pdo = new PDO("mysql:host=".$data["host"].";dbname=".$data["dbname"].";charset=utf8", $data["username"], $data["pass"]);
  $pdo-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  $sql = "SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2'";
  
  // ends up with error with no results
  $pdo-&gt;query($sql)-&gt;fetchAll();
  
  // approach #2 with PHP
  $pdo = new PDO("mysql:host=".$data["host"].";dbname=".$data["dbname"].";charset=utf8", $data["username"], $data["pass"]);
  $pdo-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  
  $resultsCounted = 5000000;
  $offset = 0;
  
  for($i = 0; $offset &lt; $resultsCounted; $i += 500) {
      $sql = "SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2' LIMIT ".$offset.", 500";
  
      // ends up with error with no results
      $result = $pdo-&gt;query($sql)-&gt;fetchAll();
  
      // do some stuff with results
      do_stuff($result);
  
      $offset += $i;
  }
  </code></pre>
  
  <p>Strangely this SQL works in PhpStorm database console when selecting "Execute to file" function:</p>
  
  <pre><code>SELECT id FROM huge_table bt INNER JOIN even_bigger_table ebt ON bt.id = ebt.id WHERE bt.column = 'data1' AND ebt.column2 = 'data2'
  </code></pre>
  
  <p>I can see how PhpStorm saves 100 by 100 results into the file until all are saved.</p>
  
  <p>I need to find a way how can I recreate PhpStorm database data saving that with PHP or find another way how to properly manage results saving.</p>