|
julien.fritsch, apparently igor was not correct. I'm looking at current 11.3 and I see that the query still runs forever.
ANALYZE looks like this:
MariaDB [test]> show analyze for 4;
|
+------+-------------+------------+----------------+--------------------------+--------------------------+---------+------+-------+----------+----------+------------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+------------+----------------+--------------------------+--------------------------+---------+------+-------+----------+----------+------------+-------------+
|
| 1 | PRIMARY | carts | ALL | NULL | NULL | NULL | NULL | 45900 | 17573.00 | 100.00 | 0.00 | Using where |
|
| 2 | SUBQUERY | line_items | index_subquery | line_items_cart_id_index | line_items_cart_id_index | 5 | func | 16 | NULL | 100.00 | NULL | Using index |
|
+------+-------------+------------+----------------+--------------------------+--------------------------+---------+------+-------+----------+----------+------------+-------------+
|
2 rows in set, 1 warning (0.000 sec)
|
(The odd thing is: it shows select_type=SUBQUERY but apparently the subquery is re-executed?)
Checking the ANALYZE FORMAT=JSON also yields odd results: it looks as if the subquery was not executed but apparently it was...
is full-scan-on-null-key not shown for some reason?
SHOW ANALYZE: {
|
"r_query_time_in_progress_ms": 387475,
|
"query_optimization": {
|
"r_total_time_ms": 0.191292083
|
},
|
"query_block": {
|
"select_id": 1,
|
"table": {
|
"delete": 1,
|
"table_name": "carts",
|
"access_type": "ALL",
|
"rows": 45900,
|
"r_rows": 28161,
|
"r_filtered": 0,
|
"r_total_time_ms": 10.67438212,
|
"r_engine_stats": {
|
"pages_accessed": 43
|
},
|
"attached_condition": "!(<in_optimizer>(carts.`id`,<exists>(subquery#2)))"
|
},
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"cost": 0.004784,
|
"having_condition": "line_items.cart_id is null",
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "line_items",
|
"access_type": "index_subquery",
|
"possible_keys": ["line_items_cart_id_index"],
|
"key": "line_items_cart_id_index",
|
"key_length": "5",
|
"used_key_parts": ["cart_id"],
|
"ref": ["func"],
|
"loops": 1,
|
"r_loops": 0,
|
"rows": 16,
|
"r_rows": null,
|
"cost": 0.004784,
|
"r_table_time_ms": 369328.7447,
|
"r_other_time_ms": 18593.83607,
|
"r_engine_stats": {
|
"pages_accessed": 977471
|
},
|
"filtered": 100,
|
"r_filtered": null,
|
"using_index": true
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
1 row in set, 1 warning (0.002 sec)
|
ANALYZE for the SELECT:
{
|
"query_optimization": {
|
"r_total_time_ms": 0.26744247
|
},
|
"query_block": {
|
"select_id": 1,
|
"cost": 7.5746156,
|
"r_loops": 1,
|
"r_total_time_ms": 33.26934857,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "carts",
|
"access_type": "ALL",
|
"loops": 1,
|
"r_loops": 1,
|
"rows": 45900,
|
"r_rows": 45811,
|
"cost": 7.5746156,
|
"r_table_time_ms": 5.313445235,
|
"r_other_time_ms": 7.150060486,
|
"r_engine_stats": {
|
"pages_accessed": 69
|
},
|
"filtered": 100,
|
"r_filtered": 0,
|
"attached_condition": "!<in_optimizer>(carts.`id`,carts.`id` in (subquery#2))"
|
}
|
}
|
],
|
"subqueries": [
|
{
|
"query_block": {
|
"select_id": 2,
|
"cost": 0.004784,
|
"r_loops": 1,
|
"r_total_time_ms": 20.78684419,
|
"nested_loop": [
|
{
|
"table": {
|
"table_name": "line_items",
|
"access_type": "index",
|
"possible_keys": ["line_items_cart_id_index"],
|
"key": "line_items_cart_id_index",
|
"key_length": "5",
|
"used_key_parts": ["cart_id"],
|
"loops": 1,
|
"r_loops": 1,
|
"rows": 92028,
|
"r_rows": 94604,
|
"cost": 13.5630447,
|
"r_table_time_ms": 9.844080129,
|
"r_other_time_ms": 10.9367028,
|
"r_engine_stats": {
|
"pages_accessed": 67
|
},
|
"filtered": 100,
|
"r_filtered": 100,
|
"using_index": true
|
}
|
}
|
]
|
}
|
}
|
]
|
}
|
}
|
.
So, still open.
|
[MDEV-21012]