Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
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
- relates to
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-
Activity
| Labels | analyze-stmt |
| Component/s | Optimizer [ 10200 ] |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
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;
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
—
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
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
—
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.1.4 [ 18400 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 60222 ] | MariaDB v3 [ 67248 ] |
| Workflow | MariaDB v3 [ 67248 ] | MariaDB v4 [ 148936 ] |
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.