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

Store UUIDs in a more efficient manner

    XMLWordPrintable

Details

    Description

      UUIDs are stored in a binary form, but not byte-swapped like in MySQL's uuid_to_bin https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin

      The order provided by UUID_TO_BIN() is not really friendly to index prefix compression:
      it only reorders the timestamp segments, while the clock-seq and the node-ID segments are still at the end. This ruins the whole idea of prefix compression.

      Let's sort UUID in a really index-friendly order, by moving the constant part to the left and the variable part to the right.

      So for comparison purposes we'll reorder segments, but keep the byte order inside the segments (i.e. in big endian order):

      MariaDB binary UUIDv1 representation, as returned by the UUID() function:
      		 
       
      		 
        llllllll-mmmm-Vhhh-vsss-nnnnnnnnnnnn
      		 
       
      		 
      Binary sortable representation:
      		 
       
      		 
         nnnnnnnnnnnn-vsss-Vhhh-mmmm-llllllll
      		 
       
      		 
      Sign           Section               Bits   Bytes  Pos   PosBinSortable
      		 
      -------------  -------               ----   -----  ---   --------------
      		 
      llllllll       time low              32     4        0   12
      		 
      mmmm           time mid              16     2        4   10
      		 
      Vhhh           version and time hi   16     2        6   8
      		 
      vsss           variant and clock seq 16     2        8   6
      		 
      nnnnnnnnnnnn   node ID               48     6       10   0

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29959 UUID Sorting

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is caused by

          Task - A task that needs to be done. MDEV-4958 Adding datatype UUID

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-20477 Merge binlog extended metadata support from the upstream

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27588 MyISAM/Aria descending index compression is not as efficient as ascending

          • Major - Major loss of function.
          • Closed
          links to

          Web Link How are GUIDs compared in SQL Server 2005?

          Web Link SQL Server GUID sort algorithm. Why?

          Web Link The uniqueidentifier data type in SQL server

          Activity

            People

              bar Alexander Barkov
              greenman Ian Gilfillan
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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