maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

Reply-To: 

Hello,

I think we have already discussed this before (multiple times), and the
idea was received with some conditional approval of the idea. Nothing has 
been done or put to paper, though, so now I'm posting this in writing. 

I'm posting this now, because we're close to RC and I think it's better to do
this kind of changes before the RC.

The proposal is: if a subquery is materialized (that is, the subquery is run
and it's output is stored with a temporary table with unique key), EXPLAIN 
should not show "SUBQUERY" (like it does now), it should show "MATERIALIZE".

Consider the following two queries. The EXPLAINs are the same, although one of
them runs materialization and the other does not:

MariaDB [test]> EXPLAIN select * from ten where a < any (select max(a) from ten);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
|  2 | SUBQUERY    | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

MariaDB [test]> EXPLAIN select * from ten where a = any (select max(a) from ten) or a < 3;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |
|  2 | SUBQUERY    | ten   | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

You can only tell that the second one uses Materialization if look at EXPLAIN
EXTENDED warning text.  I think it is extremely bad that radically new
execution strategy lurks somewhere inside EXPLAIN EXTENDED.


I remember you've used to argue that 'SUBUQERY' represents the fact that the
subquqery is evaluated once, and materialization should be shown elsewhere. I
object to that argument
- materialization is currently shown *nowhere* (except EXPLAIN EXTENDED which
  is pain to read except for the most simplest queries)
- Derived table subqueries, which are also executed once (and stored in a
  temporary table, like with materialization), have their special select_type
  value 'DERIVED':

MariaDB [test]> explain select * from one_k, (select max(A.a+B.a) from ten A, ten B ) foo;
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra                              |
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  100 |                                    |
|  1 | PRIMARY     | one_k      | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using join buffer (flat, BNL join) |
|  2 | DERIVED     | A          | ALL  | NULL          | NULL | NULL    | NULL |   10 |                                    |
|  2 | DERIVED     | B          | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using join buffer (flat, BNL join) |
+----+-------------+------------+------+---------------+------+---------+------+------+------------------------------------+
4 rows in set (0.02 sec)


I think the above should be sufficient to make a decision to change
Materialized subqueries to show 'MATERIALIZED' (or 'MATERIALIZE') in EXPLAIN
output.  Any objections?




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