Bug #67839 | created_tmp_tables and created_tmp_disk_tables | ||
---|---|---|---|
Submitted: | 7 Dec 2012 15:41 | Modified: | 21 Dec 2012 8:55 |
Reporter: | anh nx | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S5 (Performance) |
Version: | 5.1.38 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[7 Dec 2012 15:41]
anh nx
[7 Dec 2012 16:15]
Peter Laursen
.. because it is a [SESSION] status variable. The value is not for a single query but for current session/connection. Refer: http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Created_tmp_ta... .. so this particular query seems to create 3 temporary tables (in memory) every time it executes. Peter (not a MySQL/Oracle person)
[7 Dec 2012 16:31]
Hartmut Holzgraefe
Materialization of INFORMATION_SCHEMA tables requires a temp table, both your subqueries touch I_S, so that accounts for an increase by 2. And then materialization of the subquery results: yet two more, totals 4 To verify: 1) materialization of I_S tables on every use: run SELECT variable_value FROM information_schema.GLOBAL_Status WHERE variable_name = 'Created_tmp_tables'; a few times, see how the counter goes up by one each time 2) materialization of subquery in FROM clause: show status like 'Created_tmp_tables'; select a from (select 1 as a) tab; show status like 'Created_tmp_tables'; see how the counter goes up by one again
[10 Dec 2012 1:53]
anh nx
Peter Laursen and Hartmut Holzgraefe thanks for your answer more a question: How do know when MySQL Server creates tmp tables?
[21 Dec 2012 8:55]
MySQL Verification Team
Please reference the manual page for more on this: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html