Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
Description
Create test data (the below is not necessarily minimal):
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 (pk int primary key);
|
insert into t1 select a from one_k;
|
alter table t1 add b int;
|
update t1 set b=pk;
|
analyze format=json delete from t1 where pk < 10 and b > 4;
|
{
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"delete": 1,
|
"table_name": "t1",
|
"access_type": "range",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["pk"],
|
"rows": 10,
|
"r_rows": 10,
|
"r_filtered": 0.5,
|
"attached_condition": "((t1.pk < 10) and (t1.b > 4))"
|
}
|
}
|
}
|
Note that
- r_total_time_ms is missing
- r_filtered is using wrong units (it should return percentages! we can have 0.5% selectivity for 10 rows!)
Here is the SELECT for comparison:
MariaDB [test]> analyze format=json select * from t1 where pk < 10 and b > 4;
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.5577,
|
"table": {
|
"table_name": "t1",
|
"access_type": "range",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["pk"],
|
"r_loops": 1,
|
"rows": 10,
|
"r_rows": 10,
|
"r_total_time_ms": 0.3788,
|
"filtered": 100,
|
"r_filtered": 50,
|
"attached_condition": "((t1.pk < 10) and (t1.b > 4))"
|
}
|
}
|
}
|
Attachments
Issue Links
- relates to
-
MDEV-7648 Extra data in ANALYZE FORMAT=JSON $stmt
-
- Open
-
Activity
This should be fixed in bb-10.1-explain-analyze (or, in the main branch if commit f42064a172e062e04243ed4e4c2113b93bfc9136 is merged into it)
Problem#1:
- single-table UPDATE/DELETE code doesn't track its own execution time
(see
```
"r_loops": 1,
"r_total_time_ms": 0.5577,
```
in select's output. They should track it in the same way as JOIN::exec does.
Problem#2:
Single table UPDATE/DELETE doesn't install/use table io tracker (again, see how select changes handler::tracker).
Problem#3: r_filtered was not multiplied by 100 somewhere.
These all need to be fixed
revision-id: da36b92a122cb0e35ed75eff8869bc39dacf1583
parent(s): 01d7da6785284383b2c04f2d4474feccebb0bb6f
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-03-26 10:48:56 +0100
message:
MDEV-7812: ANALYZE FORMAT=JSON UPDATE/DELETE doesnt print the r_total_time_ms
Tracking total time added in UPDATE/DELETE
Fixed selectivity calculation in UPDATE/DELETE
Macro definitions of time tracting fixed.
I suppose it should read "we can NOT have 0.5% selectivity"?