On Thu, Dec 01, 2011 at 01:12:00PM +0200, Timour Katchaounov wrote:

Ok, I agree to change to MATERIALIZED (not MATERIALIZE). Who will do the change?

I could make it after I'm done with my current crashing bug.

In addition, in the EXTRA field there should be info which materialization is used. If I am not mistaken, there are three variants: - SJ-mat, - SJ-inside-out, - NON-SJ-mat (better terms are needed for explain).

(will reply to this separately)

Timour

On 1.12.2011 12:54, Sergei Petrunia wrote:

...

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