Logical optimization of joins and selections

Posted on

Question :

I am studying query optimization. I have read that it might be preferred to apply relational selections before a logical join operation. Is there a scenario where joining first and then applying selections would be desirable?

I searched for an example of this, and found that people seemed to be saying that a query with conjunctions might benefit from this, but it is not clear to me why this should be. This is not a question about how a particular query optimizer in a particular commercial product performs, it is a question of logic.

An example would be appreciated.

EDIT:

Inner Joining allows joining first and then selection and it would be optimized approach too. Example:

SELECT * FROM employee,**(Inner Joining)** department 
WHERE employee.DepartmentID=department.DepartmentID; 

Answer :

Yes, there are cases where applying the filter after the join is preferable. But they are rare.

Consider this example:

SELECT ...
FROM a
INNER JOIN b
  ON b.key = a.key
WHERE a.some_string LIKE '%foo%'

Now, assume that very few rows in a has a matching row in b. It is now faster to first join and remove the rows from a that does not match and then apply the expensive string filter.

Leave a Reply

Your email address will not be published. Required fields are marked *