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

“condition_pushdown_for_derived” optimization not used when using INSERT INTO

    XMLWordPrintable

    Details

      Description

      “condition_pushdown_for_derived” optimization correctly use index withing a simple SELECT query, but don't use it if the result records are inserted into an existing table.

      Steps to reproduce:

      CREATE TABLE `tbl` (
      `a` INT(11) NULL DEFAULT NULL,
      `b` INT(11) NULL DEFAULT NULL,
      INDEX `a` (`a`)
      );

      INSERT INTO `tbl` (`a`, `b`) VALUES
      (1, 1),
      (1, 2),
      (2, 1),
      (2, 2);

      CREATE TABLE `tbl2` (
      `a` INT(11) NULL DEFAULT NULL,
      `b` INT(11) NULL DEFAULT NULL
      );

      Simple select query:

      EXPLAIN
      SELECT * FROM
      (SELECT a,b FROM tbl GROUP BY a) t
      WHERE a=1;

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
      2 DERIVED tbl ref a a 5 const 2  

      Adding INSERT INTO index is no more used:

      EXPLAIN
      INSERT INTO tbl2
      SELECT * FROM
      (SELECT a,b FROM tbl GROUP BY a) t
      WHERE a=1;

      id select_type table type possible_keys key key_len ref rows Extra
      1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 Using where
      2 DERIVED tbl ALL NULL NULL NULL NULL 4 Using temporary; Using filesort

      For comparison, i tried a query without GROUP BY (in this case the optimization involved is derived_merge), and the optimization is maintained when using INSERT INTO

      EXPLAIN
      SELECT * FROM
      (SELECT * FROM tbl) t
      WHERE a=1;

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE tbl ref a a 5 const 2  

      EXPLAIN
      INSERT INTO tbl2
      SELECT * FROM
      (SELECT * FROM tbl) t
      WHERE a=1;

      id select_type table type possible_keys key key_len ref rows Extra
      1 SIMPLE tbl ref a a 5 const 2  

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            steris Risato Stefano
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: