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

ANALYZE FORMAT=JSON UPDATE/DELETE doesnt print the r_total_time_ms

    XMLWordPrintable

Details

    Description

      Create test data (the below is not necessarily minimal):

      create table ten(a int);
      insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table one_k(a int);
      insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
       
      create table t1 (pk int primary key);
      insert into t1 select a from one_k;
      alter table t1 add b int;
      update t1 set b=pk;

      analyze format=json delete from t1 where pk < 10 and b > 4;
      {
        "query_block": {
          "select_id": 1,
          "table": {
            "delete": 1,
            "table_name": "t1",
            "access_type": "range",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["pk"],
            "rows": 10,
            "r_rows": 10,
            "r_filtered": 0.5,
            "attached_condition": "((t1.pk < 10) and (t1.b > 4))"
          }
        }
      }

      Note that

      • r_total_time_ms is missing
      • r_filtered is using wrong units (it should return percentages! we can have 0.5% selectivity for 10 rows!)

      Here is the SELECT for comparison:

      MariaDB [test]> analyze format=json select * from t1 where pk < 10 and b > 4;
      {
        "query_block": {
          "select_id": 1,
          "r_loops": 1,
          "r_total_time_ms": 0.5577,
          "table": {
            "table_name": "t1",
            "access_type": "range",
            "possible_keys": ["PRIMARY"],
            "key": "PRIMARY",
            "key_length": "4",
            "used_key_parts": ["pk"],
            "r_loops": 1,
            "rows": 10,
            "r_rows": 10,
            "r_total_time_ms": 0.3788,
            "filtered": 100,
            "r_filtered": 50,
            "attached_condition": "((t1.pk < 10) and (t1.b > 4))"
          }
        }
      }

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.