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

subquery with group by not using index

    XMLWordPrintable

Details

    • 10.2.7-1

    Description

      Hi,

      MariaDB executes subquery with group by without filter
      before the derived table is ready for using.

      Executing large subselects with group by can take some time
      and oracle can filter this type of selects.

      Migrations from oracle to mariadb will be easier, if the optimizer can manage this.

      CREATE TABLE `t1` (
      	`id` INT(11) NOT NULL,
      	`amt` INT(11) NULL DEFAULT NULL,
      	PRIMARY KEY (`id`),
      	INDEX `amt` (`amt`)
      );
       
      CREATE TABLE `t2` (
      	`amt` INT(11) NOT NULL,
      	`somestring` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
      	INDEX `amt_ind` (`amt`)
      );
      

      Inserting some values

      INSERT INTO `t2` (`amt`, `somestring`) VALUES (4, 'AtcsBD');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (3, 'Wqjovg');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (2, 'iWjoMy');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (3, 'eoAkem');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (4, '23Dj9r');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'Im42UO');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'qbjV4X');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'BuO8oI');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (2, '6Lf5Fv');
      INSERT INTO `t2` (`amt`, `somestring`) VALUES (1, 'udFOm8');
       
       
      INSERT INTO `t1` (`id`, `amt`) VALUES (2, 1);
      INSERT INTO `t1` (`id`, `amt`) VALUES (3, 1);
      INSERT INTO `t1` (`id`, `amt`) VALUES (7, 1);
      INSERT INTO `t1` (`id`, `amt`) VALUES (10, 1);
      INSERT INTO `t1` (`id`, `amt`) VALUES (1, 2);
      INSERT INTO `t1` (`id`, `amt`) VALUES (4, 2);
      INSERT INTO `t1` (`id`, `amt`) VALUES (5, 2);
      INSERT INTO `t1` (`id`, `amt`) VALUES (9, 2);
      INSERT INTO `t1` (`id`, `amt`) VALUES (6, 3);
      INSERT INTO `t1` (`id`, `amt`) VALUES (8, 3);
      

      The explain

      explain
      select *
      from t1 a
      inner join 
      (select * from t2 group by amt) b
       on a.amt = b.amt 
      where b.amt = 1;
      

      Output shows no index is using for creating the derived table.

      MariaDB [bughunt]> explain select * from t1 a inner join  (select * from t2 group by amt) b  on a.amt = b.amt  where a.amt = 1 \G
      *************************** 1. row ***************************
                 id: 1
        select_type: PRIMARY
              table: a
               type: ref
      possible_keys: amt
                key: amt
            key_len: 5
                ref: const
               rows: 4
              Extra: Using index
      *************************** 2. row ***************************
                 id: 1
        select_type: PRIMARY
              table: <derived2>
               type: ALL
      possible_keys: NULL
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 4
              Extra: Using where; Using join buffer (flat, BNL join)
      *************************** 3. row ***************************
                 id: 2
        select_type: DERIVED
              table: t2
               type: ALL
      possible_keys: amt_ind
                key: NULL
            key_len: NULL
                ref: NULL
               rows: 10
              Extra: Using where
      3 rows in set (0.00 sec)
       
      }
      

      I don't know, if it is a bug or feature request, if initially create as a bug,
      you can change it, if you want to classify it as a feature request.

      Attachments

        1. db2.sql.gz
          8.15 MB
          Richard Stracke

        Issue Links

          Activity

            People

              igor Igor Babaev
              Richard Richard Stracke
              Votes:
              0 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.