Change max_heap_table_size value in MySQL?

The max_heap_table_size is a system variable that has both read/write property.

Initially, max_heap_table_size has size 16 MB. First, check the value of max_heap_table_size, which is in bytes.

The query is as follows −

mysql> select @@max_heap_table_size;

The following is the output −

+-----------------------+
| @@max_heap_table_size |
+-----------------------+
| 16777216              |
+-----------------------+
1 row in set (0.00 sec)

Now let us how the value 16777216 byte = 16 MB −

1 MB = 1024KB
1KB = 1024 Bytes
1MB = 1024*1024 bytes.
To convert 16777216 byte to MB you need to divide 1024*1024.
=16777216/(1024*1024)
=16777216/1048576
=16 MB

Now you can change the max_heap_table_size using SET command. The syntax is as follows −

SET @@@@max_heap_table_size=yourNumberOfBytes.

Let us change max_heap_table_size value. The number of bytes is 33554432, which is equal to 32 MB.

The query is as follows −

mysql> set @@max_heap_table_size=33554432;
Query OK, 0 rows affected (0.00 sec)

Now check the value of @@max_heap_table_size. The query is as follows −

mysql> select @@max_heap_table_size;

The following is the output −

+-----------------------+
| @@max_heap_table_size |
+-----------------------+
| 33554432              |
+-----------------------+
1 row in set (0.00 sec)

Let us see how whether it is equal to 32MB or not. The formulae used here is discussed above −

mysql> select @@max_heap_table_size/1048576 as MB;

The following is the output −

+---------+
| MB      |
+---------+
| 32.0000 |
+---------+
1 row in set (0.00 sec)