5

I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below

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

The table has around 100k rows.

I have executed the below queries and profiled the both

Query 1 (Group by on varchar column)

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

Profile 1 result

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000064 |
| checking permissions | 0.000004 |
| Opening tables       | 0.004812 |
| init                 | 0.000026 |
| System lock          | 0.000005 |
| optimizing           | 0.000006 |
| statistics           | 0.000010 |
| preparing            | 0.000006 |
| Creating tmp table   | 0.000021 |
| Sorting result       | 0.000003 |
| executing            | 0.000001 |
| Sending data         | 0.052795 |
| Creating sort index  | 0.000049 |
| end                  | 0.000003 |
| query end            | 0.000006 |
| removing tmp table   | 0.000004 |
| query end            | 0.000002 |
| closing tables       | 0.000006 |
| freeing items        | 0.000021 |
| cleaning up          | 0.000018 |
+----------------------+----------+

Temp table Details 1

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Query 2 (Group by on text column)

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

Profile 2 result

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000134 |
| checking permissions |  0.000010 |
| Opening tables       |  0.006690 |
| init                 |  0.000034 |
| System lock          |  0.000007 |
| optimizing           |  0.000004 |
| statistics           |  0.000017 |
| preparing            |  0.000008 |
| Creating tmp table   |  0.000027 |
| Sorting result       |  0.000005 |
| executing            |  0.000002 |
| Sending data         | 26.359877 |
| Creating sort index  |  0.001042 |
| end                  |  0.000007 |
| query end            |  0.000007 |
| removing tmp table   |  0.000208 |
| query end            |  0.000003 |
| closing tables       |  0.000007 |
| freeing items        |  0.000028 |
| cleaning up          |  0.000020 |
+----------------------+-----------+

Temp table Details 2

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Both profiles took almost same time in each stage expect 'Sending data stage'. For first one it took 0.052795s and for second one it took 26.359877s. Why there is that much difference in that stage? (same queries without group by clause show no difference in 'Sending data stage')

2

1 Answer 1

7

The performance difference is mainly due to the fact, that text datatype columns are always copied to temporary tables on disk, because the memory engine doesn't support those types.

From the manual:

Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 8.4.4, “Internal Temporary Table Use in MySQL”). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed.

When you don't use group by no temporary table is needed, obviously.

4
  • can you be elaborate on the difference in 'Sending data' state of the two profile outputs? May I know what is exacting going on in that state which makes such a huge difference? In my table both columns had the same value (content of varchar= content of text) Nov 9, 2018 at 12:19
  • is 'Sending data' include the overhead of On disk table processing? My guess!!! Nov 9, 2018 at 12:23
  • 1
    Unfortunately I can't. The sending data state is a mystery. Some say it includes previous steps, some don't. But my advice is simple. Don't use the profiler. The profiler is deprecated anyways and I know noone who actually uses it. You don't need to elaborate on different datatypes. Just stick to the rule, that you choose the fitting datatype and then the smallest version of this datatype as possible. Apart from that learn to optimize queries by learning how to read the result of explain <query>; and how to choose appropriate indexes. The profiler is for serious, serious debugging. Not for..
    – tombom
    Nov 9, 2018 at 12:27
  • 1
    optimizing queries.
    – tombom
    Nov 9, 2018 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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