Optimize PHP Mysqli Query Correctly

Question

I have recently had some help with INNER JOIN coding to help optimize a slow script that I have. After optimizing the remaining code and testing it I have now been able to identify that it is the queries below that are severley slowing the script down.

Can anyone help me optimize the code below to make the queries faster. The indexes are fine, I think the code is just out of date now.

        $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);

Thank you all.

Answer 1

Try getting rid of the sub-select:

Example:

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 = ????
        )
    )

Then, if it is still slow analyze it with EXPLAIN EXTENDED to see if the indexes are used correctly.

Note: The query above was not tested, it serves to give an indication. I removed the values with ???? for better readability.