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

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.

CodePudding user response：

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.

CodePudding user response：

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.