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

ANALYZE FORMAT=JSON and Range checked for each record

    XMLWordPrintable

    Details

      Description

      ANALYZE FORMAT=JSON doesn't do a good job at "Range checked for each record" queries.

      Example:

      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 (lb int, rb int);
       
      insert into t1 values (1,2);
      insert into t1 values (3,5);
      insert into t1 values (10, 20);
       
      create table t2 (key1 int, col1 int, key(key1));
      insert into t2 select a,a from ten;
      insert into t2 select 15,15 from one_k;

      explain select * from t1, t2 where t2.key1 between t1.lb and t1.rb;
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                                |
      |    1 | SIMPLE      | t2    | ALL  | key1          | NULL | NULL    | NULL | 1010 | Range checked for each record (index map: 0x1) |
      +------+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

      Ok, now ANALYZE:

      MariaDB [test]> analyze select * from t1, t2 where t2.key1 between t1.lb and t1.rb;
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                                          |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------------------+
      |    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |   3.00 |   100.00 |     100.00 |                                                |
      |    1 | SIMPLE      | t2    | ALL  | key1          | NULL | NULL    | NULL | 1010 | 338.33 |   100.00 |      99.01 | Range checked for each record (index map: 0x1) |
      +------+-------------+-------+------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------------------+

      It shows how many records we got on average. Cool.

      MariaDB [test]> analyze format=json select * from t1, t2 where t2.key1 between t1.lb and t1.rb\G
      *************************** 1. row ***************************                                                                                              
      EXPLAIN: {                                                                                                                                                  
        "query_block": {                                                                                                                                          
          "select_id": 1,                                                                                                                                         
          "r_loops": 1,                                                                                                                                           
          "r_total_time_ms": 32.893,                                                                                                                              
          "table": {                                                                                                                                              
            "table_name": "t1",                                                                                                                                   
            "access_type": "ALL",                                                                                                                                 
            "r_loops": 1,                                                                                                                                         
            "rows": 3,                                                                                                                                            
            "r_rows": 3,                                                                                                                                          
            "r_total_time_ms": 0.1226,
            "filtered": 100,
            "r_filtered": 100
          },
          "range-checked-for-each-record": {
            "keys": ["key1"],
            "table": {
              "table_name": "t2",
              "access_type": "ALL",
              "possible_keys": ["key1"],
              "r_loops": 3,
              "rows": 1010,
              "r_rows": 338.33,
              "r_total_time_ms": 16.334,
              "filtered": 100,
              "r_filtered": 99.015
            }
          }
        }
      }

      However, what is interesting is to know how many times "range checked" actually produced something, and which indexes it used. This info is not shown.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: