Re: Created_tmp_disk_tables Issue

[Rick James]

Not a bug, not even a fact; but first...

Run EXPLAIN on the SELECT. It probably says "using temporary" or "using filesort". Complicated SELECTs can create temp tables under the covers. Sometimes (not always) these lead to implicit temp tables:
* subqueries -- especially FROM ( SELECT ... )
* GROUP BY -- for sorting, to make the "GROUP BY" process easier
* ORDER BY -- for sorting

"Created_tmp_disk_tables" vs "Created_tmp_tables" -- If possible an implicit temp table will use the MEMORY engine, thereby not needing 'disk'.

OK, back to your question...

The SHOW GLOBAL STATUS command is the one that is incrementing Created_tmp_tables. This is effectively because it must convert the data from information_schema.GLOBAL_STATUS into a table, then perform the scan for the value you are requesting. You can see this by running the SHOW twice in a row.

Caveat: This 'bug' may manifest itself in different ways for different versions -- some day they may optimize information_schema access to avoid temp tables, etc. For me (on MariaDB 5.5.23), the non-disk value incremented.

GLOBAL STATUS is a great thing to have, but there are a number of anomalies, usually inconsequential. I had not known about this one. I use SHOW SESSION STATUS LIKE 'Handler%' to get insight into what a SELECT is doing. (I run that before and after the SELECT, then diff the values.)