The following binary multi-byte collations (together with their _nopad_bin counterparts):
can improve their performance if in this code in strcoll.ic:
we catch pure ASCII and try to handle 4 or even 8 bytes in one iteration by loading string data into big-endian uint32 or uint64 numbers, then comparing these two numbers.
Additionally, the following case insensitive multibyte collations (and their _nopad_ci counteparts):
can use the same idea because for ASCII they perform only a trivial mapping from lower case Latin letters [a-z] to their upper case counterparts [A-Z], and after this mapping done the comparison is performed in binary style. These collations can do the following on every iteration step:
- Test the leading 4 or 8 bytes in the two strings for pure ASCII data and go to the old code on failure (to handle multi-byte characters)
- Load the two strings into two uint32 or uint64 numbers
- Perform bulk conversion of all bytes in the two numbers from [61..7A] to [41..5A] (i.e. from [a-z] to [A-Z])
- Compare the numbers and return if they are different
- Increment pointers to 4 or 8 and continue the loop
Note, the exact way of bulk conversion of numbers to upper case is to be found out by the developer.
The performance of the low level comparison functions can be measured by the BENCHMARK() SQL functions, e.g.:
The expected performance improvement on the pure ASCII data (for strings with octet length >= 4) is between 2 and 3 times (depending on the exact length and collation).
Note, the changes must be done in a way not to bring any serious (more than 10%) slow down for:
- strings with multi-byte characters
- short strings 1..3 bytes long
MariaDB has a number of 8bit case insensitive collations with trivial toupper mapping on the ASCII range. So they can get optimized in the same way. But we'll improve these collations under terms of a separate task because they don't use the mentioned code and have their own implementations.
Also, under terms of this task we won't change the following multi-byte case insensitive collations (and their _nopad_ci counterparts):
because all these three collations additionally change the order of some ASCII punctuation characters:
|U+005D RIGHT SQUARE BRACKET
|U+005B LEFT SQUARE BRACKET
|U+005C REVERSE SOLIDUS
So on the bulk conversion step they need more efforts and the proposed optimization may not be efficient. These collations will be improved later under terms of a separate task.
These character sets have separate implementations and don't use the mentioned code. They'll be improved under terms of a separate task.