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:

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.