My Oracle Support Banner

MySQL: How to Reduce the Number of Internal Temporary Tables Created On Disk?, Created_tmp_disk_tables, Created_tmp_tables, max_heap_table_size, tmp_table_size, temptable_max_ram (Doc ID 1374804.1)

Last updated on SEPTEMBER 04, 2023

Applies to:

MySQL Server - Version 4.0 and later
Information in this document applies to any platform.

Goal

Learn about the status variables and server options related to internal temporary tables and how to avoid creating internal temporary tables on disk.

The status variables include:

  • Created_tmp_disk_tables
  • Created_tmp_tables

The server options include:

  • max_heap_table_size
  • tmp_table_size

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
 Status Variables
 Created_tmp_disk_tables
 Created_tmp_tables
 Performance Schema and sys Schema
 Current Queries Using Internal Temporary Tables
 Aggregate Statistics
 TempTable Storage Engine Usage
 Server Options
 max_heap_table_size
 tmp_table_size
 max_length_for_sort_data
 temptable_max_ram
 When is an Internal Temporary Table Created on Disk?
 How to Avoid Creating the Internal Temporary Table on Disk?
 Use MySQL 8.0 and the TempTable Storage Engine
 Rewrite Queries to Include Less Data
 Avoid BLOB and TEXT Columns
 Do not Make VARCHAR and VARBINARY Columns Wider Than Necessary
 Add Indexes to Columns Used for Sorting and Grouping
 Reduce the value of max_length_for_sort_data
 Move the Temporary Directory to a RAM Disk
 How to Tune tmp_table_size and max_heap_table_size
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.