[MDEV-7833] ANALYZE FORMAT=JSON and Range checked for each record Created: 2015-03-25  Updated: 2015-04-03  Resolved: 2015-04-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.4

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: analyze-stmt

Issue Links:
Relates
relates to MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt Open

 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.

 Comments   
Comment by Sergei Petrunia [ 2015-03-25 ]

I think, "range-checked-for-each-record" element besides "keys" should show something like this:

"r_keys" : {
 
  "key1" : nnnn,
 
  "key2" : nnnn,
 
  "index_merge" : nnn,
 
  "none" : nnnn
 
}

where nnnn are numbers how many times each option was chosen. r_keys should only list keys that are included in range-checked-for-each-record/keys.

Comment by Oleksandr Byelkin [ 2015-03-26 ]

create table t3(a int);
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t4(a int);
insert into t4 select A.a + B.a* 10 + C.a * 100 from t3 A, t3 B, t3 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 t3;
insert into t2 select 15,15 from t4;

analyze format=json
select * from t1, t2 where t2.key1 between t1.lb and t1.rb;

drop table t1,t2,t3,t4;

Comment by Oleksandr Byelkin [ 2015-04-02 ]

revision-id: fc31f6d95720b4b946b8b68c816026d65831f347
parent(s): 01d7da6785284383b2c04f2d4474feccebb0bb6f
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-04-02 18:19:33 +0200
message:

MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record

—

Comment by Oleksandr Byelkin [ 2015-04-03 ]

revision-id: 82a705badf50521de7c0d4b0713a04b018e26167
parent(s): 01d7da6785284383b2c04f2d4474feccebb0bb6f
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-04-03 14:07:47 +0200
message:

MDEV-7833:ANALYZE FORMAT=JSON and Range checked for each record

—

Comment by Sergei Petrunia [ 2015-04-03 ]

Ok to push.

Generated at Thu Feb 08 07:22:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.