Mysql – No ‘Copying to tmp table’ state in profiling a thesql query

database-internalsMySQLmysql-5.7performancequery-performance

I have a table with 100k rows

CREATE TABLE `small_table` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `varc` varchar(255) DEFAULT NULL,
  `txt` text,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8

and I had executed the following query

select varc,count(*) from small_table group by varc;

and I had profiled the same

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000060 |
| checking permissions | 0.000010 |
| Opening tables       | 0.004685 |
| init                 | 0.000025 |
| System lock          | 0.000006 |
| optimizing           | 0.000002 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000020 |
| Sorting result       | 0.000003 |
| executing            | 0.000005 |
| Sending data         | 0.001720 |
| Creating sort index  | 0.000033 |
| end                  | 0.000002 |
| query end            | 0.000004 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000004 |
| freeing items        | 0.000015 |
| cleaning up          | 0.000007 |
+----------------------+----------+

The profile has Creating tmp table state (by mysql docs it means a tmp table is created either in memory or in disk). My doubt is that the profile does not have Copying to tmp table state (during the state, The server will be copying to a temporary table in memory.) My assumption is that it has created a tmp table and not using it. Am I right?

Where is my Copying to tmp table state?

Best Answer

It just has a slightly different name. It's "Creating tmp table", which also makes more sense. MySQL knows, that a temporary table will be needed, so it generates it on the fly. The state name "copying to tmp table" is just poorly chosen.