-2
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

0

Didn't you ask this question somewhere else?

Didn't the answer come back that TEXT fields are preallocated at full size? That is the "allocation" mechanism for SELECT has a bias toward VARCHAR.

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.