show global status like '%tmp%'
will show output like:
# Variable_name, Value Created_tmp_disk_tables, 5939 Created_tmp_files, 6 Created_tmp_tables, 97525
If “Created_tmp_disk_tables” is too high it means system tmp tables do not fit into memory and MySQL must store them on disk. (check percentage “Created_tmp_disk_tables / Created_tmp_tables * 100”
Therefore check limits on your db:
I was able to decrease “Created_tmp_tables” by setting higher limits but still I have around 6% of tmp tables being stored on disk