Mysql Using Result of Subquery in Main Query

Question

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

School

school_id
child_id

Children

child_id
birth_year
name
etc

My first attempt is to use subquery, which is something like this

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

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.

So to optimize it, I'm doing this.

Recursive Query Using PHP

First, get the distinct birth year of ALL children who are in school. So I'm gonna query something like

SELECT birth_year FROM school LEFT JOIN children ON school.child_id = children.child_id

It will return data like

birth_year
==========
2009
2010
2011

Which I'm gonna use in another query in PHP (let's say I store the result in $row variable)

foreach ($row as $r){
    $new_array[] = count($this->db->get_child_data($r->birth_year)); //this is pseudocode only, to get the number of children data who have birth_year of 2009-2011
}

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.

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?

I'm trying to do this, but it ends up super slow and crashes my WAMP.

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 )

The subquery

SELECT GROUP_CONCAT(DISTINCT birth_year) FROM school LEFT JOIN children ON school.child_id = children.child_id

when run separately correctly and quickly returns

2009,2010,2011

And when I query

SELECT * FROM children WHERE birth_year IN (2009,2010,2011)

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.

Sorry for the long post and thanks in advance

Answer 1

The problem is that using subqueries in the IN or SELECT clauses often causes them to be run again for each row in the outer query. To avoid this, try joining to the subquery. This should result in the subquery being run only once, and cached.

SELECT c.* 
FROM children c, (SELECT birth_year FROM school LEFT JOIN children ON school.child_id = children.child_id GROUP BY birth_year) b
WHERE c.birth_year = b.birth_year

That said, it looks like you're just getting every child in any school anyway, so a simpler JOIN might also give you the same result.

SELECT c.* 
FROM children c, school s
WHERE c.child_id = s.child_id

If you just want to get the count of kids in each school, for each birth_year

SELECT count(c.child_id), s.* 
FROM school s 
LEFT JOIN children c ON c.child_id = s.child_id
GROUP BY s.school_id, c.birth_year

Answer 2

If you are trying to get the number of children in a birth year

SELECT COUNT (c.child_id) FROM child c INNER JOIN school s ON (c.child_id = s.child_id) GROUP BY birthyear 

If you need additional filters then you can add a where clause for the years that you want