Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
Description
Take a test dataset from MDEV-7833. Let's run it so that join buffer is used:
analyze format=json select * from t1, t2 where t2.key1+1 between t1.lb and t1.rb;
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 49.407,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.2984,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"block-nl-join": {
|
"table": {
|
"table_name": "t2",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1010,
|
"r_rows": 1010,
|
"r_total_time_ms": 20.465,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"buffer_type": "flat",
|
"join_type": "BNL",
|
"attached_condition": "((t2.key1 + 1) between t1.lb and t1.rb)",
|
"r_filtered": 33.201
|
}
|
}
|
} |
|
Here, we see that t2.r_loops=1. This is the effect of join buffering.
However, it would be nice to see join what join buffer was used. (TODO: it is always the same, right? If it is a subquery, it allocates the buffer once and never frees, right? Check this)
Attachments
Issue Links
Activity
| Affects Version/s | 10.1 [ 16100 ] |
| Fix Version/s | 10.1 [ 16100 ] |
| Component/s | Optimizer [ 10200 ] |
| Labels | analyze-stmt |
| 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+1 between t1.lb and t1.rb;
|
drop table t1,t2,t3,t4;
|
revision-id: ac53c41c7fb91f5e8cbce79efc31a8b16fd824a5
parent(s): 2b475b567e93c9a2438a835f2236ec3672e7769c
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-04-08 20:14:48 +0200
message:
MDEV-7835: ANALYZE FORMAT=JSON should show buffer sizes
—
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Oleksandr Byelkin [ sanja ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Fix Version/s | 10.1.4 [ 18400 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v2 [ 60224 ] | MariaDB v3 [ 66310 ] |
| Workflow | MariaDB v3 [ 66310 ] | MariaDB v4 [ 148938 ] |
block-nl-join should also include buffer-size element which should show what size of the buffer was used.