Details
Critical
Description
When derived tables or VIEWs are resolved with subquery merge, they still
increment Created_tmp_tables status variable and CREATED_TMP_TABLES in
performance_schema.events_statements_* tables.
Example:
create table t2 (a int); insert into t2 values (1),(2),(3); create view v2 as select a from t2;
flush status; select * from v2; show status like '%Created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+
To be sure it's merged, let's check the query plan:
explain select * from v2; +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 3 | | +------+-------------+-------+------+---------------+------+---------+------+------+-------+
The same thing happens when using a derived table:
select * from (select * from t2) T1;
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Vicentiu Ciorbaru, can you make a patch that does #1 ?
Checked where do_not_open parameter of create_tmp_table() comes from.
it was introduced by this revision:
igor@askmonty.org-20100526201818-en2jqo1p1t3hi3ra
Igor Babaev <igor@askmonty.org>
maria-5.3-mwl106
Wed 2010-05-26 13:18:18 -0700
MWL#106: Backport optimizations for derived tables and views.
The main consolidated patch.
According to the documented code:
table->created == TRUE if and only if tmp table was actually created.
This flag gets set in 2 places:
In create_internal_tmp_table(). (which is perfectly reasonable)
In open_tmp_table() function. (which is again reasonable)
To me, it makes sense to increase the status variable whenever this flag change occurs.
However, currently, the only place where this happens is in create_tmp_table()
and create_sj_weedout_tmp_table. In order to not litter the code with status var increments, I've modified
open_tmp_table and create_internal_tmp_table to do this instead.
create_tmp_table with do_not_open that does not have a subsequent open_tmp_table call:
- create_dummy_tmp_table in opt_subselect.cc. (I don't know if the table actually gets opened)
- There is a very possible bug in sql_class.cc:3676 as the parameter keep_row_order is passed as do_not_open! sql_union.cc:156 does not have this bug!
There is also an extra prototype of create_internal_tmp_table with a my_bool big_tables argument. There is no implementation or call to this function in the code anywhere, thus I've removed it.
The part about possible slowdown is branched off into MDEV-7662.
Code-wise the problem happens when create_tmp_table() is called with the parameter do_not_open=true. We create a temporary but we don't open it. The problems with this are