🚀 go-pugleaf

Bill Todd [TeamB] wrote:
> Hs wrote:
> 
>> What is happening? Generaly what is the difference in resuls and in
>> background between calling HAVING or WHERE for non aggregation fields?
> 
> In case one the optimizer elected to read farmer in natural order and
> use the primary index of buy to locate the record in buy that matched
> the current record in farmer. This means that every record in buy was
> located in the index then read from disk before the WHERE conditions
> could be applied.
> 
> In case two the optimizer elected to sort farmer, sort buy then merge
> the two streams. Although you cannot tell from the plan the WHERE and
> HAVING conditions were probably applied to buy as it was read in
> natural order so only the matching records had to be sorted and merged.
> This resulted in much less I/O than locating all of the buy records one
> at a time through the index.
> 
> Why did the optimizer elect to use the index in one case and do a sort
> merge in the other? Only one (or possibly two) people know and they are
> in InterBase R&D.:)
> 

According to "The Practical SQL Handbook", 3rd ed. the HAVING word 
causes grouping to occur first and then eliminates rows -- possibly 
cutting down the set to where a sort/merge is faster

-- 
Will R
PMC Consulting