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

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

    XMLWordPrintable

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

            igor Igor Babaev
            sanja Oleksandr Byelkin
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.