maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Hello Igor,

On the question of whether the set of used select_id allow to reconstruct the
query tree: Please find below two examples:

Example #1: select(t1) has two children, select(t2), select(t3):

MariaDB [test]> explain select * from t1 where a in (select a from t2) or b in (select a from t3);
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type  | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|    3 | MATERIALIZED | t3    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             |
|    2 | MATERIALIZED | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             |
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)

Example #2: select(t1) has a child select(t2), which has a child, select(t3):

MariaDB [test]> explain select * from t1 where a in (select a from t2 where b in (select a from t3) or b <3) or b < 3;
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type  | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | PRIMARY      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|    2 | MATERIALIZED | t2    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|    3 | MATERIALIZED | t3    | ALL  | NULL          | NULL | NULL    | NULL |    2 |             |
+------+--------------+-------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.01 sec)

The order of selects in EXPLAIN output is different, but I don't believe it
allows to reconstruct the structure of the tree in all cases. (if it does, 
is it practically useful? the function is complicated and fragile)

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