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

MyISAM/Aria descending index compression is not as efficient as ascending

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • N/A
    • N/A
    • Data types
    • None

    Description

      There was some effort made in 10.7 to store UUID efficiently (MDEV-26664).
      Apparently the logic does not apply (or doesn't apply well enough) to descending indexes which are being introduced in 10.8 in the scope of MDEV-13756.

      Below is based on type_uuid.type_uuid_myisam test.

      CREATE TABLE t1 (a UUID, KEY(a)) ENGINE=MyISAM;
       
      --delimiter $
      FOR i IN 0..8191 DO
      INSERT INTO t1 VALUES (UUID());
      END FOR $
      --delimiter ;
       
      SELECT
      INDEX_LENGTH, DATA_LENGTH, INDEX_LENGTH/DATA_LENGTH
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
       
      DROP TABLE t1;
      

      With a standard ascending key it gives the result

      INDEX_LENGTH	DATA_LENGTH	INDEX_LENGTH/DATA_LENGTH
      79872	139264	0.5735
      

      With a descending index (KEY(a DESC)) it produces

      preview-10.8-MDEV-13756-desc-indexes c10e10c6

      INDEX_LENGTH	DATA_LENGTH	INDEX_LENGTH/DATA_LENGTH
      150528	139264	1.0809
      

      Which, according to the same test's criteria, is a middle ground between "packed" and "not packed".

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.