VARCHAR vs TEXT performance when data fits on row

Question

mysql> desc temp1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| value | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

mysql> desc temp2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| value | text | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

255 - 'a' characters in each row(In both tables)

mysql> select * from temp1 limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| value                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> select * from temp2 limit 1;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| value                                                                                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Query table 1:

select count(*) from temp1 where value like '%a';

Query table 2:

select count(*) from temp2 where value like '%a';

Stats:

No of records---temp1(varchar)---temp2(text)


2097152---------6.08(sec)--------6.91(sec)          
4194304---------12.42(sec)-------13.66(sec)
8388608---------25.08(sec)-------28.03(sec)
16777216--------52.82(sec)-------56.88(sec)
33554432--------1(min)50.17(sec)-1(min)59.36(sec)

My question: How can the difference in execution speed be explained?

The rows contents are same in both tables.

As I understood VarChar and Text columns keep contents offPage only when it exceeds row size. So both tables contents will be inline data for my page size(16kb). Then what was the reason for this query execution time difference.

Note: Both table column is not indexed

Row Format - DYNAMIC

Collation - UTF8mb3

Character set - utf8_general_ci

Storage engine -  innodb

Mysql - 5.7

Reference link: https://stackoverflow.com/a/48301727/5431418

Update: Same flow now I tried with 5000 characters ('a') in both tables the result difference is high.

2097152---------1(min)53.63(sec)--------2(min)4.66(sec)    

Update 2: Same flow now I tried with 2 characters ('a') in both tables still there is a performance difference

Adding table status:

mysql> select * FROM information_schema.tables  WHERE table_schema = "db67006db" and table_name = 'temp1';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | db67006db    | temp1      | BASE TABLE | InnoDB |      10 | Dynamic    |   30625036 |            315 |  9659482112 |               0 |            0 | 425721856 |           NULL | 2019-09-23 20:20:17 | NULL        | NULL       | utf8_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.01 sec)

mysql> select * FROM information_schema.tables  WHERE table_schema = "db67006db" and table_name = 'temp2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def           | db67006db    | temp2      | BASE TABLE | InnoDB |      10 | Dynamic    |   30922268 |            315 |  9753853952 |               0 |            0 | 425721856 |           NULL | 2019-09-23 20:20:12 | NULL        | NULL       | utf8_general_ci |     NULL |                |               |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+

Answer 1

With respect to storage, InnoDB will handle VARCHAR and TEXT much the same when both stored inline. However, when fetching the data from InnoDB, the server will allocate space for all VARCHAR columns before query execution. While space for TEXT columns will only be allocated if they are actually read, where DYNAMIC memory allocation takes time.

https://forums.mysql.com/read.php?24,645115,645164#msg-645164