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