Mysql – Performance difference between Text and Varchar in Mysql

mysql-5.7performancetemporary-tablesvarchar

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')

Best Answer

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.

Related Question