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

Unable to skip filesort when using implicit extended key

    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

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

              Dates

              • Created:
                Updated:
                Resolved: