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

Unable to skip filesort when using implicit extended key

    XMLWordPrintable

Details

    Description

      The optimizer is able to skip filesort when PK columns are explicitly defined in the secondary index, but not able to do so when they are in the index extension.

      Test dataset:

      CREATE TABLE tb_bug1 (
        pk1 int(11) NOT NULL,
        pk2 varchar(64) NOT NULL,
        col1 varchar(16) DEFAULT NULL,
        PRIMARY KEY (pk1,pk2),
        KEY key1 (pk1,col1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE tb_bug2 (
        pk1 int(11) NOT NULL,
        pk2 varchar(64) NOT NULL,
        col1 varchar(16) DEFAULT NULL,
        PRIMARY KEY (pk1,pk2),
        KEY key1 (pk1,col1,pk2)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      INSERT INTO `tb_bug1` VALUES 
      (12321321,'a8f5f167f44f4964e6c998dee827110c','video'),
      (12321321,'d77a17a3659ffa60c54e0ea17b6c6d16','video'),
      (12321321,'wwafdsafdsafads','video'),
      (12321321,'696aa249f0738e8181957dd57c2d7d0b','video-2014-09-23'),
      (12321321,'802f9f29584b486f356693e3aa4ef0af','video=sdsd'),
      (12321321,'2f94543ff74aab82e9a058b4e8316d75','video=sdsdsds'),
      (12321321,'c1316b9df0d203fd1b9035308de52a0a','video=sdsdsdsdsd');
      insert into tb_bug2 select * from tb_bug1;

      explain SELECT pk2
      FROM tb_bug1 USE INDEX(key1)
      WHERE pk1 = 123 AND col1 = 'video'
      ORDER BY pk2 DESC LIMIT 21;
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  tb_bug1 ref     key1    key1    55      const,const     1       Using where; Using index; Using filesort
      explain SELECT pk2
      FROM tb_bug2 USE INDEX(key1)
      WHERE pk1 = 123 AND col1 = 'video'
      ORDER BY pk2 DESC LIMIT 21;
      id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
      1       SIMPLE  tb_bug2 ref     key1    key1    55      const,const     1       Using where; Using index

      Note that the first query uses "Using filesort" while the second one doesn't.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            1 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.