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.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
—
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
—
Ok to push.
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.