SQL查询运行超慢

Question

我在下面运行的代码运行得非常好，直到我尝试在最后一个循环中获取数量。我认为它可能可以通过JOINs等进行优化，但是在抓取DISTINCT元素时，我并不太熟悉最佳方法？

$sql_dept = "SELECT DISTINCT `department_guid` FROM `bom_material_copy` WHERE `complex_guid`='9EB75BE9-26E2-AEBB-1B52-37DBC2FB89EA' ORDER BY `id` DESC LIMIT 0,5"; 

foreach($pdo->query($sql_dept) AS $dept) 
{ 
    $sql_mat = "SELECT DISTINCT `material_bct_number` FROM `bom_material_copy` WHERE 
       `complex_guid`='9EB75BE9-26E2-AEBB-1B52-37DBC2FB89EA' AND 
       `department_guid`='".$dept['department_guid']."' 
       ORDER BY `id` DESC LIMIT 0,5"; 

    foreach($pdo->query($sql_mat) AS $mat) 
    { 
     echo "<tr>"; 
      echo "<td>".$mat['material_bct_number']."</td>"; 
      // Grab Units Again 
      $u = 0; 
      $sql_unit = "SELECT DISTINCT `unit_number` FROM `bom_projects_units` WHERE `complex_guid`='9EB75BE9-26E2-AEBB-1B52-37DBC2FB89EA'"; 
      foreach($pdo->query($sql_unit) AS $unit) 
      { 
       $sql_quan = "SELECT `quantity` FROM `bom_material_copy` 
          WHERE 
          `material_bct_number`='".$mat['material_bct_number']."' AND 
          `unit_number`='".$unit['unit_number']."' AND 
          `department_guid`='".$dept['department_guid']."'"; 

       foreach($pdo->query($sql_quan) AS $quan) 
       { 
        echo "<td>".$quan['quantity']."</td>"; 
       } 
      } 
     echo "</tr>"; 
    } 
} 

Answer 1

在您的查询中尝试使用EXPLAIN。

当你这样做时，你知道你在查询中使用的表之间的关系。

下一步，在您所匹配的字段中使用INDEX，然后再次执行您的查询。