How to find MySQL creating temporary table in memory or disk? 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.
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)]&gt; SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 6     |
| Created_tmp_tables      | 0     | &lt;--- session starts with 0 tmp tables
+-------------------------+-------+
3 rows in set (0.01 sec)

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

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

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

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

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

MariaDB [(none)]&gt; SHOW STATUS like 'Created\_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     | &lt;--- 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)]&gt; 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 &lt;---- 1 disk tmp table created
     SUM_CREATED_TMP_TABLES: 1 &lt;---- 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)]&gt; SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [(none)]&gt; 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)]&gt; 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.