Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1
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
-