SQL查询运行速度非常慢

Question

我在下面运行的代码运行得非常好，直到我尝试在最后一个循环中获取数量。我认为可以使用连接等对其进行优化，但我不太熟悉在获取不同元素时的最佳方法。

$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，然后再次执行查询。