<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="temporary-files">
      </a>
      B.3.3.5 Where MySQL Stores Temporary Files
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045053828176">
  </a>
  <a class="indexterm" name="idm46045053827136">
  </a>
  <a class="indexterm" name="idm46045053826048">
  </a>
  <p>
   On Unix, MySQL uses the value of the
   <code class="literal">
    TMPDIR
   </code>
   environment variable as the path name of the directory in
          which to store temporary files. If
   <code class="literal">
    TMPDIR
   </code>
   is not set, MySQL uses the system default, which is usually
   <code class="filename">
    /tmp
   </code>
   ,
   <code class="filename">
    /var/tmp
   </code>
   , or
   <code class="filename">
    /usr/tmp
   </code>
   .
  </p>
  <p>
   On Windows, MySQL checks in order the values of the
   <code class="literal">
    TMPDIR
   </code>
   ,
   <code class="literal">
    TEMP
   </code>
   , and
   <code class="literal">
    TMP
   </code>
   environment variables. For the first
          one found to be set, MySQL uses it and does not check those
          remaining. If none of
   <code class="literal">
    TMPDIR
   </code>
   ,
   <code class="literal">
    TEMP
   </code>
   , or
   <code class="literal">
    TMP
   </code>
   are set,
          MySQL uses the Windows system default, which is usually
   <code class="filename">
    C:\windows\temp\
   </code>
   .
  </p>
  <p>
   If the file system containing your temporary file directory is
          too small, you can use the
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   <a class="link" href="server-options.html#option_mysqld_tmpdir">
    <code class="option">
     --tmpdir
    </code>
   </a>
   option to specify a
          directory in a file system where you have enough space.
  </p>
  <p>
   The
   <a class="link" href="server-options.html#option_mysqld_tmpdir">
    <code class="option">
     --tmpdir
    </code>
   </a>
   option can be set
          to a list of several paths that are used in round-robin
          fashion. Paths should be separated by colon characters
          (
   <code class="literal">
    :
   </code>
   ) on Unix and semicolon characters
          (
   <code class="literal">
    ;
   </code>
   ) on Windows.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    To spread the load effectively, these paths should be
            located on different
    <span class="emphasis">
     <em>
      physical
     </em>
    </span>
    disks,
            not different partitions of the same disk.
   </p>
  </div>
  <p>
   If the MySQL server is acting as a replica, you can set the
          system variable
   <a class="link" href="replication-options-replica.html#sysvar_replica_load_tmpdir">
    <code class="literal">
     replica_load_tmpdir
    </code>
   </a>
   to
          specify a separate directory for holding temporary files when
          replicating
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   statements. This directory should be in a disk-based file
          system (not a memory-based file system) so that the temporary
          files used to replicate LOAD DATA can survive machine
          restarts. The directory also should not be one that is cleared
          by the operating system during the system startup process.
          However, replication can now continue after a restart if the
          temporary files have been removed.
  </p>
  <p>
   MySQL arranges that temporary files are removed if
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   is terminated. On platforms that
          support it (such as Unix), this is done by unlinking the file
          after opening it. The disadvantage of this is that the name
          does not appear in directory listings and you do not see a big
          temporary file that fills up the file system in which the
          temporary file directory is located. (In such cases,
   <span class="command">
    <strong>
     lsof +L1
    </strong>
   </span>
   may be helpful in identifying
          large files associated with
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   .)
  </p>
  <p>
   When sorting (
   <code class="literal">
    ORDER BY
   </code>
   or
   <code class="literal">
    GROUP
          BY
   </code>
   ), MySQL normally uses one or two temporary files.
          The maximum disk space required is determined by the following
          expression:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-clike"><div class="docs-select-all right" id="sa92844125"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-clike"><span class="token punctuation">(</span>length of what is sorted <span class="token operator">+</span> <span class="token function">sizeof</span><span class="token punctuation">(</span>row pointer<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token operator">*</span> number of matched rows
<span class="token operator">*</span> <span class="token number">2</span></code></pre>
  </div>
  <p>
   The row pointer size is usually four bytes, but may grow in
          the future for really big tables.
  </p>
  <p>
   For some statements, MySQL creates temporary SQL tables that
          are not hidden and have names that begin with
   <code class="filename">
    #sql
   </code>
   .
  </p>
  <p>
   Some
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   queries creates
          temporary SQL tables to hold intermediate results.
  </p>
  <p>
   DDL operations that rebuild the table and are not performed
          online using the
   <code class="literal">
    ALGORITHM=INPLACE
   </code>
   technique create a temporary copy of the original table in the
          same directory as the original table.
  </p>
  <p>
   Online DDL operations may use temporary log files for
          recording concurrent DML, temporary sort files when creating
          an index, and temporary intermediate tables files when
          rebuilding the table. For more information, see
   <a class="xref" href="innodb-online-ddl-space-requirements.html" title="17.12.3 Online DDL Space Requirements">
    Section 17.12.3, “Online DDL Space Requirements”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    InnoDB
   </code>
   user-created temporary tables and
          on-disk internal temporary tables are created in a temporary
          tablespace file named
   <code class="filename">
    ibtmp1
   </code>
   in the MySQL
          data directory. 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>
  <p>
   See also
   <a class="xref" href="innodb-information-schema-temp-table-info.html" title="17.15.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table">
    Section 17.15.7, “InnoDB INFORMATION_SCHEMA Temporary Table Info Table”
   </a>
   .
  </p>
  <p>
   The optional
   <code class="literal">
    EXTENDED
   </code>
   modifier causes
   <a class="link" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    <code class="literal">
     SHOW TABLES
    </code>
   </a>
   to list hidden
          tables created by failed
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER
          TABLE
    </code>
   </a>
   statements. See
   <a class="xref" href="show-tables.html" title="15.7.7.39 SHOW TABLES Statement">
    Section 15.7.7.39, “SHOW TABLES Statement”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
