Mysql在主要查询中使用子查询的结果
基本上,我想要做的是将某个出生年份内的所有子项都放在另一个表中。所以我有两张桌子。比方说,这是一个校表Mysql在主要查询中使用子查询的结果
学校
school_id
child_id
儿童
child_id
birth_year
name
etc
我第一次尝试是使用子查询,这是这样的
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
这个查询的问题是子查询将在整个记录中运行,所以如果我有1000条记录,我认为查询(和子查询)将在由birth_year分组之前运行1000次,这很慢,差不多是3-5秒为500个样本数据。
所以要优化它,我正在这样做。
递归查询使用PHP
首先,让所有的孩子谁在学校的不同出生年份。所以我要查询像
SELECT birth_year FROM school LEFT JOIN children ON school.child_id = children.child_id
它会返回像
birth_year
==========
2009
2010
2011
我敢在PHP另一个查询要使用的数据(比方说我,结果存储到$ row变量)
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
}
虽然它会运行额外的三个查询,但由于计数非常简单,因此速度非常快。 500个样本数据只需要不到0.5秒的时间。
但是,我想知道是否有任何方法来优化它?或者更好的是,有没有办法在具有相似性能的单个查询中执行?
我试图做到这一点,但它最终超慢,崩溃了我的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)
子查询
SELECT GROUP_CONCAT(DISTINCT birth_year) FROM school LEFT JOIN children ON school.child_id = children.child_id
当单独运行正常,并迅速返回
2009,2010,2011
当我查询
SELECT * FROM children WHERE birth_year IN (2009,2010,2011)
它也快,所以我相当困惑为什么当我加入这两个查询s,我的WAMP崩溃的速度很慢。
对不起提前
问题的长期职位和感谢的是,使用在IN
或SELECT
条款子查询往往使他们在外部查询的每一行被再次运行。为了避免这种情况,请尝试加入子查询。这应该导致子查询只运行一次,并被缓存。
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
这就是说,它看起来像你刚刚在任何一所学校得到每个孩子无论如何,所以一个简单的JOIN也可以给你同样的结果。
SELECT c.*
FROM children c, school s
WHERE c.child_id = s.child_id
如果你只是想获得孩子的数量在每所学校,每个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
如果你试图让一个出生年份儿童人数
SELECT COUNT (c.child_id) FROM child c INNER JOIN school s ON (c.child_id = s.child_id) GROUP BY birthyear
如果你需要额外的过滤器,那么你可以添加一个你想要的年的where子句
这就是我在第一次尝试时所要做的:SELECT *,(SELECT COUNT(*)FROM school LEFT JOIN children ON school.child_id = children.child_id)作为总共FROM学校LEFT JOIN children on school.child_id = children.child_id GROUP BY birth_year这很慢。我认为在子查询中加入学校与儿童是必要的,以提供更准确的结果(因此不包括未在学校的儿童) – Henson 2012-02-29 05:29:15
实际上我试过用s ubquery,看到我的第一次尝试,因为我想每年都得到孩子的总数。瓶颈似乎像计数子查询虽然... – Henson 2012-02-29 05:25:51
这里的关键是子查询的位置。但我必须说,你的最终目标是什么并不完全清楚。 – Umbrella 2012-02-29 05:37:18
我想我会用递归PHP查询,它的速度很快,我说的是4秒(子查询)与0.2秒(递归PHP)。你是对的,我的最终目标是每年都要让所有的孩子上学,最简单的方法就是递归查询,因为它让PHP能够完成艰苦的工作 – Henson 2012-02-29 05:44:15