On 02/10/2013 2:19 AM, Baruch Burstein wrote:
On Fri, Sep 13, 2013 at 5:29 AM, Ryan Johnson
<ryan.john...@cs.utoronto.ca>wrote:
-- Join cardinality: Bach was a *very* prolific composer whose output
likely dwarfs the (surviving) output of his contemporaries
select p.title, c.name, p.year from composers c join pieces p on p.c_id =
c.id where c.name like '%bach%' and p.year between 1700 and 1750
How would you mark this even using the suggested function syntax? The
likelihood of "c.name like '%bach%' " being true depends on the order the
query optimizer decides to evaluate the 2 predicates in, which in turn
depends on the likelihood of the predicate!
Predicate order doesn't matter; the optimizer will push both down to
their respective source tables before the join, where they will execute
independently of each other. The problem would come if the above join
were input to some outer query and the optimizer needed to reason about
the cardinality of the join based on the selectivity of the inputs: the
inputs would be highly filtered but the join would impose unexpectedly
little additional filtering due to the correlation between famous
composes named Bach and the half century of interest.
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
