MySQL – How to Find if Temporary Table is in Memory or Disk

innodbMySQLmysql-5.5temporary-tables

I have a query, after running it if i run the show status in the same session like below

show status where Variable_name like '%Created_tmp_disk_tables%';

It got incremented by one value. So it means temporary table is created on disk right? Not sure whether it is correct or not.

But if I enable profiling for that query. Below is the status I got.

| status                | duration  |
|---------------------- |---------- |
| starting              | 0.000246  |
| checking permissions  | 0.000035  |
| Opening tables        | 0.000098  |
| System lock           | 0.000044  |
| init                  | 0.000209  |
| optimizing            | 0.000075  |
| statistics            | 0.000336  |
| preparing             | 0.000138  |
| Creating tmp table    | 0.000435  |
| executing             | 0.000038  |
| Copying to tmp table  | 2.923929  |
| Sorting result        | 0.267926  |
| Sending data          | 0.000945  |
| end                   | 0.000071  |
| removing tmp table    | 0.077263  |
| query end             | 0.000022  |
| closing tables        | 0.000055  |
| freeing items         | 0.000092  |
| logging slow query    | 0.000074  |
| cleaning up           | 0.000022  |

Here I have found Copying to tmp table but not Copying to tmp table on disk.

Why there is a conflict between these two?

Whether Show status variable is correct or profiling is correct?

Need to know whether temporary table is created on disk or not.

Best Answer

Use the variable Created_tmp_disk_tables either on session or global scope to know when you have created implicit temporary tables on disk:

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     | <--- session starts with 0 tmp tables
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- one tmp table created, 0 on disk
+-------------------------+-------+
3 rows in set (0.01 sec)

MariaDB [(none)]> SELECT 1 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 1     | <--- no tmp table created
+-------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create table test.test (a blob);
Query OK, 0 rows affected (0.18 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     | <--- tmp table created on disk
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

If you enable performance_schema, the default configuration (very very low overhead) provides you a summary of everything you probably need:

MariaDB [(none)]> SELECT * FROM performance_schema.events_statements_summary_by_digest\G
[...]
*************************** 26. row ***************************
                SCHEMA_NAME: test
                     DIGEST: f0aad09130b2e108d8dd1c58d1713678
                DIGEST_TEXT: SELECT * FROM `test` UNION SELECT * FROM `test` 
                 COUNT_STAR: 1
             SUM_TIMER_WAIT: 716448000
             MIN_TIMER_WAIT: 716448000
             AVG_TIMER_WAIT: 716448000
             MAX_TIMER_WAIT: 716448000
              SUM_LOCK_TIME: 308000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 0
          SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 1 <---- 1 disk tmp table created
     SUM_CREATED_TMP_TABLES: 1 <---- 1 tmp table created (including the disk one)
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 3
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 1
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2018-01-06 21:18:18
                  LAST_SEEN: 2018-01-06 21:18:18

Edit: I've just noticed you are still on 5.5- I strongly suggest the upgrade, only P_S is worth it for the time not lost on debugging. For now, the session/global variable should work for you, the profiling will not work as well:

MariaDB [(none)]> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM test.test UNION SELECT * FROM test.test;
Empty set (0.00 sec)

MariaDB [(none)]> SHOW PROFILES;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00055224 | SELECT * FROM test.test UNION SELECT * FROM test.test |
+----------+------------+-------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW PROFILE FOR QUERY 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000011 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000026 |
| After opening tables | 0.000009 |
| System lock          | 0.000008 |
| Table lock           | 0.000196 |
| optimizing           | 0.000010 |
| statistics           | 0.000015 |
| preparing            | 0.000013 |
| optimizing           | 0.000007 |
| statistics           | 0.000006 |
| preparing            | 0.000009 |
| executing            | 0.000005 |
| Sending data         | 0.000019 |
| executing            | 0.000004 |
| Sending data         | 0.000009 |
| optimizing           | 0.000006 |
| statistics           | 0.000006 |
| preparing            | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.000016 |
| removing tmp table   | 0.000057 |
| Sending data         | 0.000006 |
| query end            | 0.000008 |
| closing tables       | 0.000004 |
| Unlocking tables     | 0.000008 |
| freeing items        | 0.000005 |
| updating status      | 0.000009 |
| cleaning up          | 0.000012 |
+----------------------+----------+
30 rows in set (0.00 sec)

Sometimes you will see a step like "copying from heap to myisam", when it bails on on-memory execution, but that will not happen everytime, like shown above. You mention innodb on the tags, but implicit temporary tables only started being InnoDB (vs HEAP + MyISAM) since 5.7.

Related Question