I have a table with 100k rows

CREATE TABLE `small_table` (
  `varc` varchar(255) DEFAULT NULL,
  `txt` text,
  PRIMARY KEY (`pk`)

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?

  • @tombom, can you be elaborate pls – Dinesh Kumar Nov 9 '18 at 9:36

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.

  • "The state name "copying to tmp table" is just poorly chosen." can you be elaborate on this statement pls? – Dinesh Kumar Nov 9 '18 at 9:45
  • I mean to say that the developers of MySQL could have chosen a better name, like "creating tmp table". – tombom Nov 9 '18 at 9:49
  • 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? – Dinesh Kumar Nov 9 '18 at 9:52
  • Yes, it's the same. – tombom Nov 9 '18 at 9:59

I usually find Profile to be useless. Usually "sending data" is the top entry. That gives no clue of what should be fixed to improve performance.

Although there is no "copying to tmp table", there is a rather large "opening tables". But no clue as to whether it is your table, or a tmp table. There is "Creating tmp table". The tmp table could be in memory; that is, no actual disk table.

There are two ways to do your query --

  • Build a hash in memory of all the varcs; scan the table, incrementing entries in the hash; sort the result; deliver it.

  • Sort the table first (this involves a tmp table in RAM in your case); then a simple table scan, building one COUNT at a time.

I don't know when the Optimizer picks one over the other. But it always involves a table scan, which tends to be the bulk of the time spent (in spite of it not showing up in the Profile).

If you happened to have INDEX(varc), it would have done:

  • Scan that index, building the one count at a time. This would avoid the temp table and any form of sort.

But... When a query takes under 10ms, it is not worth dissecting -- there is very little to be gained, and very little that is worth optimizing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.