最近,我对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);


谢谢你们。

最佳答案

尝试摆脱子选择:

例:

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进行分析,以查看索引是否正确使用。

注意:上面的查询未经过测试,可以提供指示。我使用????删除了这些值,以提高可读性。

关于php - 正确优化PHP Mysqli查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/31299352/

10-12 15:06
查看更多