maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Hi Timour, 

Please find below some input re MDEV-83:

* I expect that MDEV-83 will have an @@optimizer_switch flag. Is that so?


* MDEV-83 will change optimizer behavior, however the change will not be
visible in EXPLAIN.  We have a history of such attempts and we've always
ended up having to fix the EXPLAIN so that the change is visible.
Do you have any ideas how to do it?  My proposal is to show "subquery#n" 
in the Extra column of the table to which the subquery predicate is attached.


* You need to take join buffering into account. For example, for this query:
explain select * from ten A, ten B where cond(B.col) and cond(A.col, B.col);
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+
|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra                                           |
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+
| 1|SIMPLE     |A    |ALL |NULL         |NULL|NULL   |NULL|  10|                                                |
| 1|SIMPLE     |B    |ALL |NULL         |NULL|NULL   |NULL|  10|Using where; Using join buffer (flat, BNL join) |
+--+-----------+-----+----+-------------+----+-------+----+----+------------------------------------------------+

both cond(B.col) and cond(A.col, B.col) will be attached to table B, however
they will have different cardinalities.


* The same goes for semi-join strategies. It is possible to calculate output 
cardinality for any condition, however it is much less obvious.


* Suppose you have a query:
   
   select * from ot1, ot2 
   where expr in (select it1.col1 from it1, it2 where subquery_cond(it2.col2))

suppose the join order is:

    SJ-Materialize-scan(it1, it2), ot1, ot2 

Here, subquery_cond may only be attached inside the SJ-Materialization nest.
This is because subquery cond relies on the value of it2.col2, which is not 
available outside the SJ-Materialization nest.


* LEFT JOINs: when the query has outer joins, predicates that are attached to
tables that are inner w.r.t some LEFT JOIN, are wrapped in Item_func_trig_cond.
I suppose you'll reuse the wrapping code, so that repositioned conditions are
wrapped into appropriate Item_func_trig_conds. 
I'm not sure whether we should support predicate repositioning for parts of ON
expressions. I suppose not implementing this allows to deliver sooner. If this
is the case, we should not support  repositioning inside ON expressions in the
first milestone.


* Section 1.2 says: 
  " An estimate is made of the partial result cardinality, such that it takes 
    into account actual pushed predicate selectivity"

As far as I understand, you only have ability to estimate selectivity of 
predicates in form 
 
  "tbl1.col CMP const"

For such conditions, it is known in advance which table they will be attached
to. There is no need to traverse the "cheap" pushed-down conditions. Then, what 
exactly do you mean by "take into account actual pushed predicate selectivity"?


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog