<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="internal-temporary-tables">
      </a>
      10.4.4 Internal Temporary Table Use in MySQL
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045226660704">
  </a>
  <p>
   In some cases, the server creates internal temporary tables
        while processing statements. Users have no direct control over
        when this occurs.
  </p>
  <p>
   The server creates temporary tables under conditions such as
        these:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Evaluation of
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      statements, with some exceptions described later.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of some views, such those that use the
      <code class="literal">
       TEMPTABLE
      </code>
      algorithm,
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      , or aggregation.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of derived tables (see
      <a class="xref" href="derived-tables.html" title="15.2.15.8 Derived Tables">
       Section 15.2.15.8, “Derived Tables”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of common table expressions (see
      <a class="xref" href="with.html" title="15.2.20 WITH (Common Table Expressions)">
       Section 15.2.20, “WITH (Common Table Expressions)”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Tables created for subquery or semijoin materialization (see
      <a class="xref" href="subquery-optimization.html" title="10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions">
       Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table
        Expressions”
      </a>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of statements that contain an
      <code class="literal">
       ORDER
            BY
      </code>
      clause and a different
      <code class="literal">
       GROUP
            BY
      </code>
      clause, or for which the
      <code class="literal">
       ORDER
            BY
      </code>
      or
      <code class="literal">
       GROUP BY
      </code>
      contains columns
            from tables other than the first table in the join queue.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of
      <code class="literal">
       DISTINCT
      </code>
      combined with
      <code class="literal">
       ORDER BY
      </code>
      may require a temporary table.
     </p>
    </li>
    <li class="listitem">
     <p>
      For queries that use the
      <code class="literal">
       SQL_SMALL_RESULT
      </code>
      modifier, MySQL uses an in-memory temporary table, unless
            the query also contains elements (described later) that
            require on-disk storage.
     </p>
    </li>
    <li class="listitem">
     <p>
      To evaluate
      <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       <code class="literal">
        INSERT ...
            SELECT
       </code>
      </a>
      statements that select from and insert into
            the same table, MySQL creates an internal temporary table to
            hold the rows from the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      , then inserts those
            rows into the target table. See
      <a class="xref" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
       Section 15.2.7.1, “INSERT ... SELECT Statement”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of multiple-table
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of
      <a class="link" href="aggregate-functions.html#function_group-concat">
       <code class="literal">
        GROUP_CONCAT()
       </code>
      </a>
      or
      <a class="link" href="aggregate-functions.html#function_count">
       <code class="literal">
        COUNT(DISTINCT)
       </code>
      </a>
      expressions.
     </p>
    </li>
    <li class="listitem">
     <p>
      Evaluation of window functions (see
      <a class="xref" href="window-functions.html" title="14.20 Window Functions">
       Section 14.20, “Window Functions”
      </a>
      ) uses temporary tables as
            necessary.
     </p>
    </li>
   </ul>
  </div>
  <p>
   To determine whether a statement requires a temporary table, use
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   and check the
   <code class="literal">
    Extra
   </code>
   column to see whether it says
   <code class="literal">
    Using temporary
   </code>
   (see
   <a class="xref" href="using-explain.html" title="10.8.1 Optimizing Queries with EXPLAIN">
    Section 10.8.1, “Optimizing Queries with EXPLAIN”
   </a>
   ).
   <code class="literal">
    EXPLAIN
   </code>
   does not necessarily say
   <code class="literal">
    Using temporary
   </code>
   for
        derived or materialized temporary tables. For statements that
        use window functions,
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   with
   <code class="literal">
    FORMAT=JSON
   </code>
   always provides information
        about the windowing steps. If the windowing functions use
        temporary tables, it is indicated for each step.
  </p>
  <p>
   Some query conditions prevent the use of an in-memory temporary
        table, in which case the server uses an on-disk table instead:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Presence of a
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      column in the table. The
      <code class="literal">
       TempTable
      </code>
      storage engine, which is the
            default storage engine for in-memory internal temporary
            tables in MySQL 8.4, supports binary large
            object types. See
      <a class="xref" href="internal-temporary-tables.html#internal-temporary-tables-engines" title="Internal Temporary Table Storage Engine">
       Internal Temporary Table Storage Engine
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Presence of any string column with a maximum length larger
            than 512 (bytes for binary strings, characters for nonbinary
            strings) in the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      list,
            if
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION
       </code>
      </a>
      or
      <a class="link" href="union.html" title="15.2.18 UNION Clause">
       <code class="literal">
        UNION ALL
       </code>
      </a>
      is used.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="show-columns.html" title="15.7.7.6 SHOW COLUMNS Statement">
       <code class="literal">
        SHOW COLUMNS
       </code>
      </a>
      and
      <a class="link" href="describe.html" title="15.8.1 DESCRIBE Statement">
       <code class="literal">
        DESCRIBE
       </code>
      </a>
      statements use
      <code class="literal">
       BLOB
      </code>
      as the type for some columns, thus
            the temporary table used for the results is an on-disk
            table.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The server does not use a temporary table for
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   statements that meet
        certain qualifications. Instead, it retains from temporary table
        creation only the data structures necessary to perform result
        column typecasting. The table is not fully instantiated and no
        rows are written to or read from it; rows are sent directly to
        the client. The result is reduced memory and disk requirements,
        and smaller delay before the first row is sent to the client
        because the server need not wait until the last query block is
        executed.
   <a class="link" href="explain.html" title="15.8.2 EXPLAIN Statement">
    <code class="literal">
     EXPLAIN
    </code>
   </a>
   and optimizer
        trace output reflects this execution strategy: The
   <code class="literal">
    UNION RESULT
   </code>
   query block is not present
        because that block corresponds to the part that reads from the
        temporary table.
  </p>
  <p>
   These conditions qualify a
   <code class="literal">
    UNION
   </code>
   for
        evaluation without a temporary table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The union is
      <code class="literal">
       UNION ALL
      </code>
      , not
      <code class="literal">
       UNION
      </code>
      or
      <code class="literal">
       UNION
            DISTINCT
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      There is no global
      <code class="literal">
       ORDER BY
      </code>
      clause.
     </p>
    </li>
    <li class="listitem">
     <p>
      The union is not the top-level query block of an
      <code class="literal">
       {INSERT | REPLACE} ... SELECT ...
      </code>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="internal-temporary-tables-engines">
       </a>
       Internal Temporary Table Storage Engine
      </h4>
     </div>
    </div>
   </div>
   <p>
    An internal temporary table can be held in memory and
          processed by the
    <code class="literal">
     TempTable
    </code>
    or
    <code class="literal">
     MEMORY
    </code>
    storage engine, or stored on disk by
          the
    <code class="literal">
     InnoDB
    </code>
    storage engine.
   </p>
   <h5>
    <a name="idm46045226590512">
    </a>
    Storage Engine for In-Memory Internal Temporary Tables
   </h5>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_internal_tmp_mem_storage_engine">
     <code class="literal">
      internal_tmp_mem_storage_engine
     </code>
    </a>
    variable defines the storage engine used for in-memory
          internal temporary tables. Permitted values are
    <code class="literal">
     TempTable
    </code>
    (the default) and
    <code class="literal">
     MEMORY
    </code>
    .
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Configuring a session setting for
     <a class="link" href="server-system-variables.html#sysvar_internal_tmp_mem_storage_engine">
      <code class="literal">
       internal_tmp_mem_storage_engine
      </code>
     </a>
     requires the
     <a class="link" href="privileges-provided.html#priv_session-variables-admin">
      <code class="literal">
       SESSION_VARIABLES_ADMIN
      </code>
     </a>
     or
     <a class="link" href="privileges-provided.html#priv_system-variables-admin">
      <code class="literal">
       SYSTEM_VARIABLES_ADMIN
      </code>
     </a>
     privilege.
    </p>
   </div>
   <p>
    The
    <code class="literal">
     TempTable
    </code>
    storage engine provides
          efficient storage for
    <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
     <code class="literal">
      VARCHAR
     </code>
    </a>
    and
    <a class="link" href="binary-varbinary.html" title="13.3.3 The BINARY and VARBINARY Types">
     <code class="literal">
      VARBINARY
     </code>
    </a>
    columns, and
          other binary large object types.
   </p>
   <p>
    The following variables control
    <code class="literal">
     TempTable
    </code>
    storage engine limits and behavior:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       : Defines
              the maximum size of any individual in-memory internal
              temporary table created using the
       <code class="literal">
        TempTable
       </code>
       storage engine. When the
              limit determined by
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       is
              reached, MySQL automatically converts the in-memory
              internal temporary table to an
       <code class="literal">
        InnoDB
       </code>
       on-disk internal temporary table. The default value is
              16777216 bytes (16 MiB).
      </p>
      <p>
       The
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       limit
              is intended to prevent individual queries from consuming
              an inordinate amount of global
       <code class="literal">
        TempTable
       </code>
       resources, which can affect
              the performance of concurrent queries that require such
              resources. Global
       <code class="literal">
        TempTable
       </code>
       resources
              are controlled by
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_mmap">
        <code class="literal">
         temptable_max_mmap
        </code>
       </a>
       .
      </p>
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       is less
              than
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       ,
              it is not possible for an in-memory temporary table to use
              more than
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       .
              If
       <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
        <code class="literal">
         tmp_table_size
        </code>
       </a>
       is
              greater than the sum of
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_mmap">
        <code class="literal">
         temptable_max_mmap
        </code>
       </a>
       , an
              in-memory temporary table cannot use more than the sum of
              the
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_mmap">
        <code class="literal">
         temptable_max_mmap
        </code>
       </a>
       limits.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       :
              Defines the maximum amount of RAM that can be used by the
       <code class="literal">
        TempTable
       </code>
       storage engine before it
              starts allocating space from memory-mapped files or before
              MySQL starts using
       <code class="literal">
        InnoDB
       </code>
       on-disk
              internal temporary tables, depending on your
              configuration. If not set explicitly, the value of
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       is 3%
              of the total memory available on the server, with a
              minimum of 1 GB and a maximum of 4 GB.
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
         <code class="literal">
          temptable_max_ram
         </code>
        </a>
        does
                not account for the thread-local memory block allocated
                to each thread that uses the
        <code class="literal">
         TempTable
        </code>
        storage engine. The size of
                the thread-local memory block depends on the size of the
                thread's first memory allocation request. If the
                request is less than 1MB, which it is in most cases, the
                thread-local memory block size is 1MB. If the request is
                greater than 1MB, the thread-local memory block is
                approximately the same size as the initial memory
                request. The thread-local memory block is held in
                thread-local storage until thread exit.
       </p>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="server-system-variables.html#sysvar_temptable_use_mmap">
        <code class="literal">
         temptable_use_mmap
        </code>
       </a>
       :
              Controls whether the
       <code class="literal">
        TempTable
       </code>
       storage
              engine allocates space from memory-mapped files or MySQL
              uses
       <code class="literal">
        InnoDB
       </code>
       on-disk internal temporary
              tables when the limit determined by
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       is
              exceeded. The default value is
       <code class="literal">
        OFF
       </code>
       .
      </p>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        <a class="link" href="server-system-variables.html#sysvar_temptable_use_mmap">
         <code class="literal">
          temptable_use_mmap
         </code>
        </a>
        is
                deprecated; expect support for it to be removed in a
                future version of MySQL. Setting
        <a class="link" href="server-system-variables.html#sysvar_temptable_max_mmap">
         <code class="literal">
          temptable_max_mmap=0
         </code>
        </a>
        is
                equivalent to setting
        <a class="link" href="server-system-variables.html#sysvar_temptable_use_mmap">
         <code class="literal">
          temptable_use_mmap=OFF
         </code>
        </a>
        .
       </p>
      </div>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_mmap">
        <code class="literal">
         temptable_max_mmap
        </code>
       </a>
       : Sets
              the maximum amount of memory the
       <code class="literal">
        TempTable
       </code>
       storage engine is permitted
              to allocate from memory-mapped files before MySQL starts
              using
       <code class="literal">
        InnoDB
       </code>
       on-disk internal temporary
              tables. The default value is
       <code class="literal">
        0
       </code>
       (disabled). The limit is intended to address the risk of
              memory mapped files using too much space in the temporary
              directory (
       <a class="link" href="server-system-variables.html#sysvar_tmpdir">
        <code class="literal">
         tmpdir
        </code>
       </a>
       ).
       <code class="literal">
        temptable_max_mmap = 0
       </code>
       disables
              allocation from memory-mapped files, effectively disabling
              their use, regardless of the value of
       <a class="link" href="server-system-variables.html#sysvar_temptable_use_mmap">
        <code class="literal">
         temptable_use_mmap
        </code>
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    Use of memory-mapped files by the
    <code class="literal">
     TempTable
    </code>
    storage engine is governed by these rules:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Temporary files are created in the directory defined by
              the
       <a class="link" href="server-system-variables.html#sysvar_tmpdir">
        <code class="literal">
         tmpdir
        </code>
       </a>
       variable.
      </p>
     </li>
     <li class="listitem">
      <p>
       Temporary files are deleted immediately after they are
              created and opened, and therefore do not remain visible in
              the
       <a class="link" href="server-system-variables.html#sysvar_tmpdir">
        <code class="literal">
         tmpdir
        </code>
       </a>
       directory. The
              space occupied by temporary files is held by the operating
              system while temporary files are open. The space is
              reclaimed when temporary files are closed by the
       <code class="literal">
        TempTable
       </code>
       storage engine, or when the
       <code class="literal">
        mysqld
       </code>
       process is shut down.
      </p>
     </li>
     <li class="listitem">
      <p>
       Data is never moved between RAM and temporary files,
              within RAM, or between temporary files.
      </p>
     </li>
     <li class="listitem">
      <p>
       New data is stored in RAM if space becomes available
              within the limit defined by
       <a class="link" href="server-system-variables.html#sysvar_temptable_max_ram">
        <code class="literal">
         temptable_max_ram
        </code>
       </a>
       .
              Otherwise, new data is stored in temporary files.
      </p>
     </li>
     <li class="listitem">
      <p>
       If space becomes available in RAM after some of the data
              for a table is written to temporary files, it is possible
              for the remaining table data to be stored in RAM.
      </p>
     </li>
    </ul>
   </div>
   <p>
    When using the
    <code class="literal">
     MEMORY
    </code>
    storage engine for
          in-memory temporary tables
          (
    <a class="link" href="server-system-variables.html#sysvar_internal_tmp_mem_storage_engine">
     <code class="literal">
      internal_tmp_mem_storage_engine=MEMORY
     </code>
    </a>
    ),
          MySQL automatically converts an in-memory temporary table to
          an on-disk table if it becomes too large. The maximum size of
          an in-memory temporary table is defined by the
    <a class="link" href="server-system-variables.html#sysvar_tmp_table_size">
     <code class="literal">
      tmp_table_size
     </code>
    </a>
    or
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    value,
          whichever is smaller. This differs from
    <code class="literal">
     MEMORY
    </code>
    tables explicitly created with
    <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
     <code class="literal">
      CREATE TABLE
     </code>
    </a>
    . For such tables,
          only the
    <a class="link" href="server-system-variables.html#sysvar_max_heap_table_size">
     <code class="literal">
      max_heap_table_size
     </code>
    </a>
    variable determines how large a table can grow, and there is
          no conversion to on-disk format.
   </p>
   <h5>
    <a name="internal-temporary-tables-engines-disk">
    </a>
    Storage Engine for On-Disk Internal Temporary Tables
   </h5>
   <p>
    MySQL 8.4 uses only the
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    storage engine for on-disk
          internal temporary tables. (The
    <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
     <code class="literal">
      MYISAM
     </code>
    </a>
    storage engine is no
          longer supported for this purpose.)
   </p>
   <p>
    <code class="literal">
     InnoDB
    </code>
    on-disk internal temporary tables
          are created in session temporary tablespaces that reside in
          the data directory by default. For more information, see
    <a class="xref" href="innodb-temporary-tablespace.html" title="17.6.3.5 Temporary Tablespaces">
     Section 17.6.3.5, “Temporary Tablespaces”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="internal-temporary-tables-storage">
       </a>
       Internal Temporary Table Storage Format
      </h4>
     </div>
    </div>
   </div>
   <p>
    When in-memory internal temporary tables are managed by the
    <code class="literal">
     TempTable
    </code>
    storage engine, rows that include
    <code class="literal">
     VARCHAR
    </code>
    columns,
    <code class="literal">
     VARBINARY
    </code>
    columns, and other binary large
          object type columns are represented in memory by an array of
          cells, with each cell containing a
    <code class="literal">
     NULL
    </code>
    flag, the data length, and a data pointer. Column values are
          placed in consecutive order after the array, in a single
          region of memory, without padding. Each cell in the array uses
          16 bytes of storage. The same storage format applies when the
    <code class="literal">
     TempTable
    </code>
    storage engine allocates space
          from memory-mapped files.
   </p>
   <p>
    When in-memory internal temporary tables are managed by the
    <code class="literal">
     MEMORY
    </code>
    storage engine, fixed-length row
          format is used.
    <code class="literal">
     VARCHAR
    </code>
    and
    <code class="literal">
     VARBINARY
    </code>
    column values are padded to the
          maximum column length, in effect storing them as
    <code class="literal">
     CHAR
    </code>
    and
    <code class="literal">
     BINARY
    </code>
    columns.
   </p>
   <p>
    Internal temporary tables on disk are always managed by
    <code class="literal">
     InnoDB
    </code>
    .
   </p>
   <p>
    When using the
    <code class="literal">
     MEMORY
    </code>
    storage engine,
          statements can initially create an in-memory internal
          temporary table and then convert it to an on-disk table if the
          table becomes too large. In such cases, better performance
          might be achieved by skipping the conversion and creating the
          internal temporary table on disk to begin with. The
    <a class="link" href="server-system-variables.html#sysvar_big_tables">
     <code class="literal">
      big_tables
     </code>
    </a>
    variable can be
          used to force disk storage of internal temporary tables.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="internal-temporary-tables-monitoring">
       </a>
       Monitoring Internal Temporary Table Creation
      </h4>
     </div>
    </div>
   </div>
   <p>
    When an internal temporary table is created in memory or on
          disk, the server increments the
    <a class="link" href="server-status-variables.html#statvar_Created_tmp_tables">
     <code class="literal">
      Created_tmp_tables
     </code>
    </a>
    value.
          When an internal temporary table is created on disk, the
          server increments the
    <a class="link" href="server-status-variables.html#statvar_Created_tmp_disk_tables">
     <code class="literal">
      Created_tmp_disk_tables
     </code>
    </a>
    value. If too many internal temporary tables are created on
          disk, consider adjusting the engine-specific limits described
          in
    <a class="xref" href="internal-temporary-tables.html#internal-temporary-tables-engines" title="Internal Temporary Table Storage Engine">
     Internal Temporary Table Storage Engine
    </a>
    .
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Due to a known limitation,
     <a class="link" href="server-status-variables.html#statvar_Created_tmp_disk_tables">
      <code class="literal">
       Created_tmp_disk_tables
      </code>
     </a>
     does not count on-disk temporary tables created in
            memory-mapped files. By default, the TempTable storage
            engine overflow mechanism creates internal temporary tables
            in memory-mapped files. See
     <a class="xref" href="internal-temporary-tables.html#internal-temporary-tables-engines" title="Internal Temporary Table Storage Engine">
      Internal Temporary Table Storage Engine
     </a>
     .
    </p>
   </div>
   <p>
    The
    <code class="literal">
     memory/temptable/physical_ram
    </code>
    and
    <code class="literal">
     memory/temptable/physical_disk
    </code>
    Performance
          Schema instruments can be used to monitor
    <code class="literal">
     TempTable
    </code>
    space allocation from memory and
          disk.
    <code class="literal">
     memory/temptable/physical_ram
    </code>
    reports
          the amount of allocated RAM.
    <code class="literal">
     memory/temptable/physical_disk
    </code>
    reports the
          amount of space allocated from disk when memory-mapped files
          are used as the TempTable overflow mechanism. If the
    <code class="literal">
     physical_disk
    </code>
    instrument reports a value
          other than 0 and memory-mapped files are used as the TempTable
          overflow mechanism, a TempTable memory limit was reached at
          some point. Data can be queried in Performance Schema memory
          summary tables such as
    <a class="link" href="performance-schema-memory-summary-tables.html" title="29.12.20.10 Memory Summary Tables">
     <code class="literal">
      memory_summary_global_by_event_name
     </code>
    </a>
    .
          See
    <a class="xref" href="performance-schema-memory-summary-tables.html" title="29.12.20.10 Memory Summary Tables">
     Section 29.12.20.10, “Memory Summary Tables”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
