我创建了以下查询:
$query = "SELECT COUNT(sire_id) as c, sire_id FROM dog WHERE sire_id IS NOT NULL GROUP BY sire_id ORDER BY c desc LIMIT 150 ";
$result = mysql_query($query) ;
$i=0;
while ($line = mysql_fetch_array($result)) {
$sireQuery = "SELECT name, id, sire_id, dam_id, yearofbirth FROM dog WHERE id = " . $line["sire_id"];
$sireResult = mysql_query($sireQuery) ;
$sireLine = mysql_fetch_array($sireResult);
$sr = mysql_query("SELECT name FROM dog WHERE id = " . $sireLine["sire_id"]);
$sl = mysql_fetch_array($sr);
$dr = mysql_query("SELECT name FROM dog WHERE id = " . $sireLine["dam_id"]);
$dl = mysql_fetch_array($dr);
$queryMS = "SELECT COUNT(DISTINCT dam_id) FROM dog WHERE sire_id = " . $line["sire_id"];
$resultMS = mysql_query($queryMS);
$totalMS = mysql_result($resultMS,0);
$i++;
现在尝试通过添加以下内容来获取更多信息
$queryoffS = "SELECT COUNT(id) FROM dog WHERE sire_id IN (SELECT id FROM dog WHERE sire_id = " .$line[sire_id]. ")";
$resultoffS = mysql_query($queryoffS);
$totaloffS = mysql_result($resultoffS,0);
$queryoffD = "SELECT COUNT(id) FROM dog WHERE dam_id IN (SELECT id FROM dog WHERE sire_id = " .$line[sire_id]. ")";
$resultoffD = mysql_query($queryoffD);
$totaloffD = mysql_result($resultoffD,0);
$totaloffspring = $totaloffS += $totaloffD;
但是,将额外的内容添加到我的查询中后,页面加载速度非常慢。我想知道是否有更好的方法来编写此查询以使其快速加载?
修订后的查询,我认为可以通过单个查询为您提供所需的计数。
SELECT dog.name, dog.id, dog.sire_id, dog.dam_id, dog.yearofbirth, parent_sire.name, parent_dam.name,
COUNT(DISTINCT child.id) AS NumberOfChildren,
COUNT(DISTINCT grandchild.id) AS NumberOfGrandChildren
FROM dog
LEFT OUTER JOIN dog AS parent_sire
ON dog.sire_id = parent_sire.id
LEFT OUTER JOIN dog AS parent_dam
ON dog.dam_id = parent_dam.id
LEFT OUTER JOIN
(
SELECT id, sire_id AS parent_id
FROM dog
UNION
SELECT id, dam_id AS parent_id
FROM dog
) AS child
ON dog.id = child.parent_id
LEFT OUTER JOIN
(
SELECT id, sire_id AS parent_id
FROM dog
UNION
SELECT id, dam_id AS parent_id
FROM dog
) AS grandchild
ON child.id = grandchild.parent_id
GROUP BY dog.name, dog.id, dog.sire_id, dog.dam_id, dog.yearofbirth, parent_sire.name, parent_dam.name