Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26572

Improve simple multibyte collation performance on the ASCII range




      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.


      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
      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.


        Issue Links



              serg Sergei Golubchik
              bar Alexander Barkov
              0 Vote for this issue
              2 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.