英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论