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

second time execution of derived tables (SP/PS) do not use indices.

    Details

      Description

      EXPAIN shows the same plan, but ANALYSE STATEMENT shows reality: full table scan on second execution

      create table t1 (mat_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, matintnum CHAR(6) NOT NULL, test MEDIUMINT UNSIGNED NULL);
      create table t2 (mat_id MEDIUMINT UNSIGNED NOT NULL, pla_id MEDIUMINT UNSIGNED NOT NULL);
      insert into t1 values (NULL, 'a', 1), (NULL, 'b', 2), (NULL, 'c', 3), (NULL, 'd', 4), (NULL, 'e', 5), (NULL, 'f', 6), (NULL, 'g', 7), (NULL, 'h', 8), (NULL, 'i', 9);
      insert into t2 values (1, 100), (1, 101), (1, 102), (2, 100), (2, 103), (2, 104), (3, 101), (3, 102), (3, 105);
       
      create procedure p1()
       EXPLAIN SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
       
      create procedure p2()
       ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
       
      call p1();
      call p1();
      call p2();
      call p2();
       
      prepare stmt1 from "ANALYZE FORMAT=JSON SELECT STRAIGHT_JOIN d.pla_id, m2.mat_id FROM t1 m2 INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum";
       
      execute stmt1;
      execute stmt1;
       
      drop procedure p1,p2;
      drop table t1,t2;
      

      for first execution:

      "table": {
      "table_name": "<derived2>",
      "access_type": "ref",
      "possible_keys": ["key0"],
      "key": "key0",
      "key_length": "7",
      "used_key_parts": ["matintnum"],
      "ref": ["test.m2.matintnum"],
      "r_loops": 9,
      "rows": 2,
      "r_rows": 0.6667,
      "r_total_time_ms": 0.0207,
      "filtered": 100,
      "r_filtered": 100,
      "materialized": {

      second execution:
      "block-nl-join": {
      "table":

      { "table_name": "<derived2>", "access_type": "ALL", "r_loops": 1, "rows": 6, "r_rows": 6, "r_total_time_ms": 0.0011, "filtered": 100, "r_filtered": 100 }

      ,
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BNL",
      "attached_condition": "(d.matintnum = m2.matintnum)",
      "r_filtered": 11.111,
      "materialized": {

        Attachments

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              sanja Oleksandr Byelkin
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: