php - IN 查询计数随着页面加载速度非常慢，寻找另一个解决方案

Question

我创建了以下查询:

$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; 

但是，在我的查询中添加额外的内容后，页面加载速度非常慢。我想知道是否有更好的方法来编写这个查询，以便它加载速度快？

Answer 1

修改后的查询，我认为它将通过单个查询为您提供所需的计数。

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