title: Mysql Using Result of Subquery in Main Query
body: <p>Basically, what I want to do is to get all children in another table within a certain birth year. So I have two tables. Let's say it's a school table</p>
  
  <p><strong>School</strong></p>
  
  <pre><code>school_id
  child_id
  </code></pre>
  
  <p><strong>Children</strong></p>
  
  <pre><code>child_id
  birth_year
  name
  etc
  </code></pre>
  
  <p>My first attempt is to use subquery, which is something like this</p>
  
  <pre><code>SELECT *, (SELECT COUNT(*) FROM school LEFT JOIN children ON school.child_id = children.child_id) as total FROM school LEFT JOIN children ON school.child_id = children.child_id GROUP BY birth_year
  </code></pre>
  
  <p>The problem with this query is the subquery will run all throughout the records, so if I have 1000 records, I think the query (and the subquery) will run 1000 times before grouping by birth_year, which is slow, it's almost 3-5 seconds for 500 sample data.</p>
  
  <p>So to optimize it, I'm doing this.</p>
  
  <p><strong>Recursive Query Using PHP</strong></p>
  
  <p>First, get the distinct birth year of ALL children who are in school. So I'm gonna query something like</p>
  
  <pre><code>SELECT birth_year FROM school LEFT JOIN children ON school.child_id = children.child_id
  </code></pre>
  
  <p>It will return data like</p>
  
  <pre><code>birth_year
  ==========
  2009
  2010
  2011
  </code></pre>
  
  <p>Which I'm gonna use in another query in PHP (let's say I store the result in $row variable)</p>
  
  <pre><code>foreach ($row as $r){
      $new_array[] = count($this-&gt;db-&gt;get_child_data($r-&gt;birth_year)); //this is pseudocode only, to get the number of children data who have birth_year of 2009-2011
  }
  </code></pre>
  
  <p>Though it will run additional three queries, this is really fast as the count is simple. It only takes less than 0.5 seconds for 500 sample data.</p>
  
  <p>However, I'm wondering if there's any way of optimizing it? Or better, is there a way to do it in a single query with similar performance?</p>
  
  <p>I'm trying to do this, but it ends up super slow and crashes my WAMP.</p>
  
  <pre><code>SELECT * FROM children WHERE birth_year IN (SELECT GROUP_CONCAT(DISTINCT birth_year) FROM school LEFT JOIN children ON school.child_id = children.child_id )
  </code></pre>
  
  <p>The subquery </p>
  
  <pre><code>SELECT GROUP_CONCAT(DISTINCT birth_year) FROM school LEFT JOIN children ON school.child_id = children.child_id
  </code></pre>
  
  <p>when run separately correctly and quickly returns</p>
  
  <pre><code>2009,2010,2011
  </code></pre>
  
  <p>And when I query</p>
  
  <pre><code>SELECT * FROM children WHERE birth_year IN (2009,2010,2011)
  </code></pre>
  
  <p>It also works fast , so I'm quite confused why when I join both queries, it is slow to the point to crash my WAMP.</p>
  
  <p>Sorry for the long post and thanks in advance</p>