CODEWITHSUNDEEP
phpmysqli
NOJ75
NOJ75

Reputation: 63

mySql INNER JOIN Confusion

I have been racking my brains for ages trying to figure this out but just can't seem to get my head round it. There is plenty of information out there but I have been unable to get it right. I understand that using the following method is not good:

WHERE id IN (SELECT.....

I am trying to optimize the query below as I have many of this type in one script and it is making the page dreadfully slow:

$res = mysqli_query($DB, 'SELECT * FROM nde WHERE id IN (SELECT relto FROM ft_n_rel WHERE rel_fr = '.$DB->real_escape_string($rid).' AND rel_ty = '.$DB->real_escape_string($FA).') LIMIT 1');

Trying to do that query optimizes with INNER JOIN is completely baffling me.

Can anyone help?

Regards

Upvotes: 0

Views: 48

Answers (1)

Mark
Mark

Reputation: 5777

Sub-selects and in array clauses are always very slow.

Try:

SELECT n.* 
FROM nde n
    INNER JOIN ft_n_rel f ON n.id = f.relto
WHERE f.rel_fr = '.$DB->real_escape_string($rid).' 
    AND f.rel_ty = '.$DB->real_escape_string($FA).'
LIMIT 1

@daremachine has a good point as well, run your select with EXPLAIN or EXPLAIN EXTENDED to check if indexes exist and are properly used.

Update To check the page with explain prefix you query as follows:

EXPLAIN
SELECT n.* 
FROM nde n
    INNER JOIN ft_n_rel f ON n.id = f.relto
WHERE f.rel_fr = '.$DB->real_escape_string($rid).' 
    AND f.rel_ty = '.$DB->real_escape_string($FA).'
LIMIT 1

Upvotes: 1

Related Questions