SQL:选择TEXT字段的子字符串是否比整个值更快

huangapple go评论72阅读模式
英文:

SQL: Is selecting Substring of TEXT field faster than whole value

问题

如果我有TEXT数据类型(最多65,535个字符),取前500个字符是否比整列更快?在这种情况下可以使用LEFT函数,但我想知道它是否会提高性能或降低性能,因为毕竟它是一个函数。

英文:

If I have TEXT data type (up to 65,535 characters), is taking first 500 characters faster than the whole column? There is a LEFT function that can particularly be used in this situation, but I'm wondering if it improves performance or maybe downgrades it, since it is a function after all.

答案1

得分: 2

In addition to Bill Karwin's answer...

You can easily check that with the BENCHMARK() function, which shows that there is no difference.

SET @a:= repeat(uuid(), 65535 / 32);
Query OK, 0 rows affected (0.001 sec)

SELECT BENCHMARK(10000000, LEFT(@a,500));
1 row in set (16.017 sec)

SELECT BENCHMARK(10000000, @a);
1 row in set (16.031 sec)

If you do a SELECT with LEFT() it will be, of course, much faster since network traffic (but also memory usage) is much smaller.

英文:

In addition to Bill Karwin's answer..

You can easily check that with the BENCHMARK() function, which shows that there is no difference.

SET @a:= repeat(uuid(), 65535 / 32);
Query OK, 0 rows affected (0,001 sec)

SELECT BENCHMARK(10000000, LEFT(@a,500));
1 row in set (16,017 sec)

SELECT BENCHMARK(10000000, @a);
1 row in set (16,031 sec)

If you do a SELECT with LEFT() it will be of course much faster, since network traffic (but also memory usage) is much smaller.

答案2

得分: 1

The InnoDB storage engine has to read the full TEXT content regardless. The LEFT() function operates on the full string. It doesn't have any way of telling the storage engine to read only part of the string.

In an RDBMS where functions had intimate knowledge of the storage format, string functions like LEFT() could be optimized in clever ways.

But MySQL has a distinct plugin architecture to implement a variety of storage engines. The storage code is separate from storage-independent things like built-in string functions. So a string function has no opportunity to request part of a TEXT column.

The code that implements MySQL's LEFT() function is here: https://github.com/mysql/mysql-server/blob/8.0/sql/item_strfunc.cc#L1443-L1461

The only optimization is that it checks the length of the string. If the string is already shorter than the requested substring, it just returns the whole string. This implies that the full string must be available to check the length.

英文:

The InnoDB storage engine has to read the full TEXT content regardless. The LEFT() function operates on the full string. It doesn't have any way of telling the storage engine to read only part of the string.

In an RDBMS where functions had intimate knowledge of the storage format, string functions like LEFT() could be optimized in clever ways.

But MySQL has a distinct plugin architecture to implement ar variety of storage engines. The storage code is separate from storage-independent things like builtin string functions. So a string function has no opportunity to request part of a TEXT column.

The code that implements MySQL's LEFT() function is here: https://github.com/mysql/mysql-server/blob/8.0/sql/item_strfunc.cc#L1443-L1461

The only optimization is that it checks the length of the string. If the string is already shorter than the requested substring, it just returns the whole string. This implies that the full string must be available to check the length.

huangapple
  • 本文由 发表于 2023年2月6日 02:38:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75354621.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定