Database Administration
mysql performance mysql-5.7 database-internals query-performance
Updated Fri, 20 May 2022 19:24:15 GMT

No 'Copying to tmp table' state in profiling a mysql query


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?




Solution

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.





Comments (4)

  • +0 – "The state name "copying to tmp table" is just poorly chosen." can you be elaborate on this statement pls? — Nov 09, 2018 at 09:45  
  • +0 – I mean to say that the developers of MySQL could have chosen a better name, like "creating tmp table". — Nov 09, 2018 at 09:49  
  • +0 – Oh.. I am understanding that both 'Creating tmp table' and 'Copying to tmp table' refers to the same action that the "server is creating a tmp table". Am I right? — Nov 09, 2018 at 09:52  
  • +0 – Yes, it's the same. — Nov 09, 2018 at 09:59