Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
N/A
-
None
Description
CREATE OR REPLACE TABLE t ( |
f1 text,
|
f2 int, |
f3 varchar(16), |
f4 text,
|
f5 blob,
|
f6 blob,
|
f7 text,
|
f8 blob,
|
f9 text
|
) ENGINE=MyISAM;
|
|
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t; |
|
|
INSERT INTO t SELECT |
REPEAT('a',900), |
seq%8+1,
|
CONCAT('s',seq%8+1), |
NULL, |
REPEAT('b',900), |
REPEAT('c',100*(seq%8+1)), |
NULL, |
REPEAT('d',700), |
REPEAT('e',800) |
FROM seq_1_to_128; |
|
|
ANALYZE TABLE t PERSISTENT FOR ALL; |
|
|
SELECT v.f2 FROM |
v, t AS t_outer |
LEFT JOIN |
v AS v_inner |
JOIN |
t AS t_inner |
ON (t_inner.f3 = v_inner.f3) |
ON (t_outer.f2 = v_inner.f2) |
GROUP BY v.f2 |
HAVING v.f2 != 5; |
On a non-debug build on my machine, with SINGLE_PREC_HB the query takes less than a second. With JSON_HB it takes over 20 seconds.
|
SINGLE_PREC_HB |
+------+ |
| f2 |
|
+------+ |
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
| 6 |
|
| 7 |
|
| 8 |
|
+------+ |
7 rows in set (0.893 sec) |
|
JSON_HB |
| f2 |
|
+------+ |
| 1 |
|
| 2 |
|
| 3 |
|
| 4 |
|
| 6 |
|
| 7 |
|
| 8 |
|
+------+ |
7 rows in set (20.850 sec) |
The effect seems to be deterministic.
Analyze output:
|
SINGLE_PREC_HB |
ANALYZE: {
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 877.7945293, |
"filesort": { |
"sort_key": "v.f2", |
"r_loops": 1, |
"r_total_time_ms": 0.006179985, |
"r_used_priority_queue": false, |
"r_output_rows": 7, |
"r_buffer_size": "360", |
"r_sort_mode": "sort_key,rowid", |
"temporary_table": { |
"table": { |
"table_name": "t_outer", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 0.088546344, |
"r_other_time_ms": 0.222168455, |
"filtered": 100, |
"r_filtered": 100 |
},
|
"table": { |
"table_name": "<derived3>", |
"access_type": "ref", |
"possible_keys": ["key0"], |
"key": "key0", |
"key_length": "5", |
"used_key_parts": ["f2"], |
"ref": ["test.t_outer.f2"], |
"r_loops": 128, |
"rows": 18, |
"r_rows": 16, |
"r_table_time_ms": 2.18024053, |
"r_other_time_ms": 0.398318093, |
"filtered": 100, |
"r_filtered": 100, |
"attached_condition": "trigcond(trigcond(t_outer.f2 is not null))", |
"materialized": { |
"query_block": { |
"select_id": 3, |
"r_loops": 1, |
"r_total_time_ms": 0.838371151, |
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 0.139922486, |
"r_other_time_ms": 0.16433142, |
"filtered": 100, |
"r_filtered": 100 |
}
|
}
|
}
|
},
|
"table": { |
"table_name": "t_inner", |
"access_type": "ALL", |
"r_loops": 2048, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 174.9042608, |
"r_other_time_ms": 17.27683035, |
"filtered": 100, |
"r_filtered": 12.5, |
"attached_condition": "trigcond(t_inner.f3 = v_inner.f3)" |
},
|
"block-nl-join": { |
"table": { |
"table_name": "<derived2>", |
"access_type": "ALL", |
"r_loops": 470, |
"rows": 128, |
"r_rows": 112, |
"r_table_time_ms": 18.4123725, |
"r_other_time_ms": 663.6949674, |
"filtered": 100, |
"r_filtered": 100, |
"attached_condition": "v.f2 <> 5" |
},
|
"buffer_type": "flat", |
"buffer_size": "256Kb", |
"join_type": "BNL", |
"r_filtered": 100, |
"materialized": { |
"query_block": { |
"select_id": 2, |
"r_loops": 1, |
"r_total_time_ms": 0.476350437, |
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 0.14055453, |
"r_other_time_ms": 0.140725081, |
"filtered": 100, |
"r_filtered": 87.5, |
"attached_condition": "t.f2 <> 5" |
}
|
}
|
}
|
}
|
}
|
}
|
}
|
}
|
|
JSON_HB |
ANALYZE: {
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 21109.07323, |
"filesort": { |
"sort_key": "v.f2", |
"r_loops": 1, |
"r_total_time_ms": 0.008015922, |
"r_used_priority_queue": false, |
"r_output_rows": 7, |
"r_buffer_size": "360", |
"r_sort_mode": "sort_key,rowid", |
"temporary_table": { |
"table": { |
"table_name": "<derived2>", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 112, |
"r_rows": 112, |
"r_table_time_ms": 0.309109608, |
"r_other_time_ms": 0.066946494, |
"filtered": 100, |
"r_filtered": 100, |
"attached_condition": "v.f2 <> 5", |
"materialized": { |
"query_block": { |
"select_id": 2, |
"r_loops": 1, |
"r_total_time_ms": 0.475056252, |
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 0.130221113, |
"r_other_time_ms": 0.148690841, |
"filtered": 87.5, |
"r_filtered": 87.5, |
"attached_condition": "t.f2 <> 5" |
}
|
}
|
}
|
},
|
"block-nl-join": { |
"table": { |
"table_name": "t_outer", |
"access_type": "ALL", |
"r_loops": 27, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 2.909438633, |
"r_other_time_ms": 1.070110557, |
"filtered": 100, |
"r_filtered": 100 |
},
|
"buffer_type": "flat", |
"buffer_size": "14Kb", |
"join_type": "BNL", |
"r_filtered": 100 |
},
|
"table": { |
"table_name": "<derived3>", |
"access_type": "ref", |
"possible_keys": ["key0"], |
"key": "key0", |
"key_length": "5", |
"used_key_parts": ["f2"], |
"ref": ["test.t_outer.f2"], |
"r_loops": 14336, |
"rows": 18, |
"r_rows": 16, |
"r_table_time_ms": 174.6954054, |
"r_other_time_ms": 42.59518564, |
"filtered": 100, |
"r_filtered": 100, |
"attached_condition": "trigcond(trigcond(t_outer.f2 is not null))", |
"materialized": { |
"query_block": { |
"select_id": 3, |
"r_loops": 1, |
"r_total_time_ms": 0.81681143, |
"table": { |
"table_name": "t", |
"access_type": "ALL", |
"r_loops": 1, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 0.111641028, |
"r_other_time_ms": 0.161612628, |
"filtered": 100, |
"r_filtered": 100 |
}
|
}
|
}
|
},
|
"table": { |
"table_name": "t_inner", |
"access_type": "ALL", |
"r_loops": 229376, |
"rows": 128, |
"r_rows": 128, |
"r_table_time_ms": 19197.38184, |
"r_other_time_ms": 1689.314608, |
"filtered": 100, |
"r_filtered": 12.5, |
"attached_condition": "trigcond(t_inner.f3 = v_inner.f3)" |
}
|
}
|
}
|
}
|
}
|
Attachments
Issue Links
- is caused by
-
MDEV-21130 Histograms: use JSON as on-disk format
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Open
-