Performance difference between Text and Varchar in Mysql

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

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.

StackExchange Database Administrators Q#222176, answer score: 7