single sweep multi join in combination with where
When joining multiple tables in combination with a inner join I normally do as much filtering possible in the on clause.
Because of the single sweep multijoin this would make the joins go faster, because when more is filtered in a before join less is read in a next join.
When does a WHERE happen. After all joins as final filter, or as an intelligent filter system which tries to filter beforehand?
So would the first query be faster than the second? (not reallive example)
SELECT *
FROM `t1`
INNER JOIN `t2`
ON `t1`.`id`=`t2`.`id`
AND `t1`.`current`='y'
AND `t2`.`deleted`='n'
INNER JOIN `t1`
ON `t1`.`t3_id`=`t3`.`id`
AND `t3`.`deleted`='n'
SELECT *
FROM `t1`
INNER JOIN `t2`
ON `t1`.`id`=`t2`.`id`
INNER JOIN `t1`
ON `t1`.`t3_id`=`t3`.`id`
WHERE
`t1`.`current`='y'
AND `t2`.`deleted`='n'
AND `t3`.`deleted`='n'
Subject
Views
Written By
Posted
single sweep multi join in combination with where
5750
July 27, 2008 07:03AM
2335
August 31, 2008 11:57PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.