Binary collations to be improved
The following binary multi-byte collations (together with their _nopad_bin counterparts):
- big5_bin
- cp932_bin
- eucjpms_bin
- euckr_bin
- gb2312_bin
- gbk_bin
- sjis_bin
- ujis_bin
- utf8mb3_bin
- utf8mb4_bin
can improve their performance if in this code in strcoll.ic:
static int
|
MY_FUNCTION_NAME(strnncollsp)(CHARSET_INFO *cs __attribute__((unused)),
|
const uchar *a, size_t a_length,
|
const uchar *b, size_t b_length)
|
{
|
const uchar *a_end= a + a_length;
|
const uchar *b_end= b + b_length;
|
for ( ; ; )
|
{
|
int a_weight, b_weight, res;
|
uint a_wlen= MY_FUNCTION_NAME(scan_weight)(&a_weight, a, a_end);
|
...
|
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.
Case insensitive collations to be improved
Additionally, the following case insensitive multibyte collations (and their _nopad_ci counteparts):
- utf8mb3_general_ci
- utf8mb3_general_mysql500_ci
- utf8mb4_general_ci
- cp932_japanese_ci
- eucjpms_japanese_ci
- euckr_korean_ci
- sjis_japanese_ci
- ujis_japanese_ci
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.
Requirements
The performance of the low level comparison functions can be measured by the BENCHMARK() SQL functions, e.g.:
SET NAMES utf8mb3 COLLATE utf8mb3_general_ci;
|
SELECT BENCHMARK(10000000,'aaaaaaaaaaaaaaaa'='aaaaaaaaaaaaaaaa');
|
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
Collations that won't be changed in this task
8bit case insensitive collations
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.
Three Chinese case insensitive collations
Also, under terms of this task we won't change the following multi-byte case insensitive collations (and their _nopad_ci counterparts):
- big5_chinese_ci
- gb2312_chinese_ci
- gbk_chinese_ci
because all these three collations additionally change the order of some ASCII punctuation characters:
| Weight |
Character name |
Character |
| 0x5B |
U+005D RIGHT SQUARE BRACKET |
] |
| 0x5C |
U+005B LEFT SQUARE BRACKET |
[ |
| 0x5D |
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.
Case insensitive _general_ci collations for ucs2, utf16, utf32
These character sets have separate implementations and don't use the mentioned code. They'll be improved under terms of a separate task.
|
all.txt