On Tue, Feb 5, 2013 at 2:39 AM, Josef Kokeš <[email protected]>wrote:
> > > > 2) LEFT JOIN followed by referencing to a field in the table being NOT > > NULL makes the LEFT JOIN in reality become a (inner) JOIN, sometimes > > that's OK for optimization, but you haven't mentioned that being the > > case here. > > Maybe my queries are wrong, but my experience has been that LEFT JOIN + > IS NOT NULL is very often far faster than an (INNER) JOIN. > > I can't argue with your experience, but can wonder if it generalizes. Firebird generally optimizes queries with joins by constructing a series of nested-loop retrievals looking up records from each loop-level by key values. Often the order of the loops matters. For example, if you want to look up order information for orders last Tuesday from customers using Visa credit cards that expire in 2015 who shipped their orders to Ohio, you could be looking at the Orders table, the Customers table, and the Destinations table. Assume that all three tables include a CustomerID and that there's an index on the Date in the Orders table, on the CardType and ExpiryDate in the Customers table, and on the State in the Destinations table, the loops could be Customers -> Orders -> Destinations or Destinations ->Customers -> Orders or Orders->Customers->Destinations - or several others, even worse. The Firebird optimizer considers all the possibilities and picks the one that will minimize the number of records that must be read to resolve the query. That process cannot happen with outer joins, unless the query compiler is clever enough to recognize a NOT NULL on the right side of a left join and turn it into an inner join. A left outer join that does not exclude nulls in the right side must make the left side the outermost loop, followed by the right side. Maybe in your experience, you always choose the optimal join order and sometimes the Firebird optimizer does not. It depends on statistics that may not be up to date. Even then, the result should be equivalent to inner joins, not faster. In theory. Good luck, Ann [Non-text portions of this message have been removed]
