Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7586

Merged derived tables/VIEWs increment created_tmp_tables

    XMLWordPrintable

    Details

      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;

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: