MySql: Find out percentage of tables created on disk

README.md

Find out percentage of tables created on disk

mysql> show global status like 'created_tmp_disk_tables';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Created_tmp_disk_tables | 278571 |
+-------------------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp_tables';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| Created_tmp_tables | 1617536 |
+--------------------+---------+
1 row in set (0.00 sec)

Tmp_disk_tables=((created_tmp_disk_tables*100/(created_tmp_tables+created_tmp_disk_tables))
= ((278571*100/(1617536 + 278571))
= 14.69%

If it exceeds 25% you may consider to increase tmp_table_size

sql.sql

SELECT ROUND(
  (SELECT @a:=VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') * 100 
  /
  (
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') 
    + 
    @a
   ), 2
 )