title: How to optimize MySQLl code that select for 300.000 rows +
body: <p>I need to make XML files for a table that contains 300k+ records.</p>
  <p>The code takes around 3~4s to finish (Is this acceptable?).</p>
  <p>Add to that the data retrieval from MySQL, which takes around ~32s to finish (Is this acceptable?):</p>
  <h3>Query</h3>
  <pre><code>SELECT `id`, `join_at` 
  FROM girls g 
  WHERE g.del_flg = 0 
  ORDER BY g.join_at, g.id ASC
  </code></pre>
  <p>If I run this single query from the navicat mysql side it still takes around ~20s.</p>
  <h3>What I tried:</h3>
  <ol>
  <li><p>At first, the select query did not work because of a &quot;memory exhausted&quot; error (php.ini - memory_limit = 128M)</p>
  </li>
  <li><p>After that I changed <code>memory_limit</code> to -1. But I see that many people tell it's bad to change <code>memory_limit</code> into -1</p>
  </li>
  </ol>
  <p>So how to optimize the <code>select</code> query for 300k+ records in case of:</p>
  <ol>
  <li><p>using PHP, sql, DOMDocument code only</p>
  </li>
  <li><p>Use options from #1 combined with an indexed column in the database</p>
  </li>
  <li><p>anything else that you know ...</p>
  </li>
  </ol>
  <h3>PHP code with SQL query:</h3>
  <pre><code>public function getInfo() {
      MySQL::connect();
      try {
          $select = 'SELECT `id`, `join_at`';
          $sql = ' FROM girls g';
          $sql .= ' WHERE g.del_flg = 0';
          $sql .= ' ORDER BY g.join_at, g.id ASC';
          $sql = sprintf($sql, $this-&gt;table);
          MySQL::$sth = MySQL::$pdo-&gt;prepare($select . $sql);
          MySQL::$sth-&gt;execute();
          while($rows = MySQL::$sth-&gt;fetch(\PDO::FETCH_ASSOC)) {
              $values[] = array('id' =&gt; $rows['id'], 'join_at' =&gt; $rows['join_at']);
          }
          // $rows = MySQL::$sth-&gt;fetchAll(\PDO::FETCH_ASSOC);
      } catch (\PDOException $e) {
          return null;
      }
      return $values;
  }
  </code></pre>
  <p>I found out that <code>ORDER BY g.join_at, g.id ASC</code> part impacts the execution time. When I remove it, and use PHP instead for sorting, the execution time decreases from ~50s total to ~5s.</p>
  <p>One more thing is that if I set <code>memory_limit</code> to 128M it leads to a &quot;memory exhausted&quot; error (512M will work). Is there any other solution for this problem?</p>
  <p>Here are the indexes I currently have on the table:</p>
  <p><img src="https://i.stack.imgur.com/uFIvE.png" alt="Index" /></p>