php - SQL 查询运行速度超慢

Question

我在下面运行的代码运行得非常好，直到我尝试在最后一个循环中获取数量。我认为它可能可以通过 JOIN 等进行优化，但我不太熟悉在获取 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在您要匹配的字段中，然后再次执行您的查询。