php - 正确优化 PHP Mysqli 查询

Question

我最近在 INNER JOIN 编码方面获得了一些帮助，以帮助优化我的缓慢脚本。在优化剩余的代码并对其进行测试之后，我现在已经能够确定是下面的查询严重减慢了脚本的速度。

任何人都可以帮我优化下面的代码以使查询更快。索引很好，我认为代码现在已经过时了。

        $cqry = "SELECT * FROM ftree_node WHERE id IN ";
        if($wrow['A'] == 'F') {
            $cqry .= '(SELECT DISTINCT t1.relation_from FROM ftree_tree_node_relation AS t1, ftree_tree_node_relation AS t2 WHERE ';
            $cqry .= '(t1.relation_from=t2.relation_from) AND (t1.relation_type <> t2.relation_type) AND ';
            $cqry .= '(t1.relation_to ='.$treeDB->real_escape_string($rrow[id]).' AND t1.relation_type="'.$treeDB->real_escape_string($FAT).'") AND (t2.relation_to = '.$treeDB->real_escape_string($wrow[id]).' AND t2.relation_type = "'.$treeDB->real_escape_string($MOT).'"))';
        } else {
            $cqry .= '(SELECT DISTINCT t1.relation_from FROM ftree_tree_node_relation AS t1, ftree_tree_node_relation AS t2 WHERE ';
            $cqry .= '(t1.relation_from=t2.relation_from) AND (t1.relation_type <> t2.relation_type) AND ';
            $cqry .= '(t1.relation_to = '.$treeDB->real_escape_string($rrow[id]).' AND t1.relation_type="'.$treeDB->real_escape_string($MOT).'") AND (t2.relation_to = '.$treeDB->real_escape_string($wrow[id]).' AND t2.relation_type = "'.$treeDB->real_escape_string($FAT).'"))';
        }
        $cres = $treeDB->query($cqry);

谢谢大家。

Answer 1

尝试去掉子选择:

示例:

SELECT fn.* 
FROM ftree_node fn
    INNER JOIN ftree_tree_node_relation ftnr1 ON ftnr1.relation_from = fn.id
    INNER JOIN ftree_tree_node_relation ftnr2 ON ftnr2.relation_from = fn.id
WHERE
    ftnr1.relation_type <> ftnr2.relation_type
    AND (ftnr1.relation_to = ????
        AND t1.relation_type= ????
        AND (
            ftnr2.relation_to = ????
                    AND ftnr2.relation_type = ????
        )
    )

然后，如果仍然很慢，请使用EXPLAIN EXTENDED进行分析，看看索引是否使用正确。

注意:上面的查询未经测试，仅供引用。为了更好的可读性，我用 ??? 删除了这些值。