Tmp_table_size
If the temporary table in the memory exceeds this value, MySQL automatically converts it to the MyISAM table on the hard disk. If you execute many advanced group by queries with a large amount of memory, you can increase the value of tmp_table_size.
Max_heap_table_size
This variable sets the maximum size of the MEMORY (HEAP) Table space that can be increased. This variable is used to calculate the MAX_ROWS value of the MEMORY table. Setting this variable on an existing memory table does not work unless you use the create table or truncate table statement to recreate the TABLE.
How to Set:
Method 1: modify my. ini or my. cnf
Tmp_table_size = 64 M
Max_heap_table_size = 32 M
The biggest disadvantage of this method is that, once mysql is restarted, everything is restored to the default value.
Method 2: mysqld command line
Mysqld_safe -- key_buffer_size = 512 K -- sort_buffer_size = 16 K
-- Tmp_table_size = 64 M -- max_heap_table_size = 32 M &
Method 3: Dynamic Modification
Because tmp_table_size and max_heap_table_size both have session scopes, do as much as possible.
Set session tmp_table_size = 64*1024*1024
Set session max_heap_table_size = 32*1024*1024
How to view the modified information:
View server configuration information
Show variables where Variable_name LIKE % tmp_table_size %
View server running information
SHOW STATUS
How to Use JDBC to obtain their values:
SELECT @ tmp_table_size
SELECT @ max_heap_table_size