Is there a performance difference between varchar and text field types in TiDB?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB下面 varchar和text字段类型的性能会不会存在差异

| username: wuweizhan

Regarding the usage environment of TiDB:

Is there a performance difference between varchar and text field types in TiDB? Is the implementation consistent with MySQL? What are the differences in the underlying KV storage data structure?

2
| username: xiaohetao | Original post link

I also want to know the answer to this question.

3
| username: alfred | Original post link

There shouldn’t be any difference at the KV layer, right?

4
| username: jiyf | Original post link

Of course, between varchar and text types, you should use varchar whenever possible. Only when varchar is really insufficient should you consider the latter. This is consistent with the choice used in MySQL. In TiDB, there is no significant difference in encoding, and storage is also the same, both are encoded in the value of the row record.

5
| username: wuweizhan | Original post link

According to MySQL documentation:

  • TEXT columns can only be indexed for a specified length. MySQL doesn’t index the full length of TEXT data types (except for full-text search indexes).
  • The full length of the string can’t be used for sorting.
  • Queries involving TEXT columns can lead to the creation of a temporary table on disk instead of in memory. This is because MySQL’s memory storage engine does not support TEXT data types. There is a significant performance penalty to using disk-based tables, which means queries using TEXT can be much slower.
  • TEXT data may be stored off the table, with a pointer to the string stored on the table. Accessing data stored in this way is slower. VARCHAR data is always stored on the table. If data is frequently retrieved, inline storage offers faster performance.

May I ask, is TEXT data in TiDB also stored off the table, and is accessing data stored in this way slower?

6
| username: TiDBer_VZbEArnr | Original post link

For queries, will there be different query optimizations for varchar and text types?

7
| username: jiyf | Original post link

MySQL stores the text field separately, which TiDB does not do. TiDB should not have this performance difference.

8
| username: wuweizhan | Original post link

Got it, thank you~

9
| username: 特雷西-迈克-格雷迪 | Original post link

Key-value, similar to Redis.

10
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.