When are subselects faster than Joins?

[Robert DiFalco]

Are there any hard and fast rules for this? If someone has already
compiled a list I'd love to see it. 

For example:

   * When a subselect will eliminate duplicates a join might introduce.

Change:

SELECT DISTINCT Acl.* 
FROM Acl
  JOIN Link ON Link.childID = Acl.ID
  JOIN Paths ON Link.parentID = Path.descendantID
WHERE Path.ancestorID = '12345';

To:

SELECT Acl.* 
FROM Acl
WHERE Acl.ID IN (
  SELECT Link.childID
  FROM Link
  JOIN Paths ON Link.parentID = Path.descendantID
  WHERE Path.ancestorID = '12345' );

When table "Acl" might have many duplicates and VARCHAR fields.

Also, lets take something simple. For example I have a one to many table
of some kind of Objects to Strings. Lets call the tables Food and
Category. Would the subselect version below be faster than the JOIN
version or just the same?

SELECT Food.*
FROM Food
JOIN FoodCategory ON Food.categoryID = FoodCategory.ID
WHERE FoodCategory.name LIKE '%rui%';

Or:

SELECT Food.*
FROM Food
WHERE Food.categoryID IN
(
   SELECT
   FoodCategory.ID
   FROM FoodCategory
   WHERE FoodCategory.name LIKE '%rui%'
);

Or in this case will the JOIN as a rule be faster? 

R.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]