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

Extended keys: index properties depend on index order

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1
    • Fix Version/s: 10.1.16
    • Component/s: Optimizer
    • Labels:
      None

      Description

      Discovered this while working on MDEV-10325:
      https://jira.mariadb.org/browse/MDEV-10325?focusedCommentId=84856&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-84856

      I've narrowed it down to two tables, with identical columns and indexes, except
      that the order of indexes is different:

      create table t200 (
        index_id bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
        index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
        index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
        index_date_updated int(10) unsigned DEFAULT NULL ,
       
        PRIMARY KEY (index_id),
        KEY object (index_class(181),index_object_id),
        KEY index_date_updated (index_date_updated)
      ) engine=innodb;
      

      create table t200swp (
        index_id bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
        index_class varchar(265) COLLATE latin1_general_ci DEFAULT NULL ,
        index_object_id int(10) unsigned NOT NULL DEFAULT '0' ,
        index_date_updated int(10) unsigned DEFAULT NULL ,
       
        PRIMARY KEY (index_id),
        KEY index_date_updated (index_date_updated),
        KEY object (index_class(181),index_object_id)
      ) engine=innodb;
      

      Now, look at the key defintions.

      (gdb) p table->s.table_name
        $209 = {str = 0x7fff501ae65c "t200", length = 4}
      (gdb) p table->key_info[0]
        $210 = {key_length = 8, flags = 1, user_defined_key_parts = 1,
        usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 1, 
        ext_key_part_map = 0, ... name = 0x7fff501a4f99 "PRIMARY",... }
      (gdb) p table->key_info[1]
        $211 = {key_length = 188, flags = 104, user_defined_key_parts = 2, 
        usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 104, 
        ext_key_part_map = 0, ... name = 0x7fff501a4fa1 "object" ... }
      (gdb) p table->key_info[2]
        $212 = {key_length = 5, flags = 64, user_defined_key_parts = 1, 
        usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 64, 
        ext_key_part_map = 0, ... name = 0x7fff501a4fa8 "index_date_updated"}
      

      (gdb) p table->s.table_name
        $216 = {str = 0x7fff501b25bc "t200swp", length = 7}
      (gdb) p table->key_info[0]
        $217 = {key_length = 8, flags = 1, user_defined_key_parts = 1, 
         usable_key_parts = 1, ext_key_parts = 1, ext_key_flags = 1, 
         ext_key_part_map = 0, ... name = 0x7fff501b4479 "PRIMARY"}
      (gdb) p table->key_info[1]
        $218 = {key_length = 5, flags = 64, user_defined_key_parts = 1, 
        usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 131136, 
        ext_key_part_map = 1, ... name = 0x7fff501b4481 "index_date_updated"}
      (gdb) p table->key_info[2]
        $219 = {key_length = 188, flags = 104, user_defined_key_parts = 2, 
        usable_key_parts = 2, ext_key_parts = 2, ext_key_flags = 104, 
        ext_key_part_map = 0, ... name = 0x7fff501b4494 "object", }
      

      Compare the attributes of 'index_date_updated' key:

      • t200swp has user_defined_key_parts=1, ext_key_parts=2, correct.
      • t200 has: user_defined_key_parts=1, ext_key_parts=1. Why 1?

      I could understand that there may be some limitations that prevent inclusion of
      PK components into the secondary key (like maximum index tuple length perhaps?)
      What I don't understand is why the order of indexes in the table plays a role.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: