|
M-A-X,
Thanks for the report and test case.
Reproducible on all 10.2 versions, with MyISAM and InnoDB. Not reproducible on 5.5-10.1 or MySQL 5.7.
|
10.2 ANALYZE JSON
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.4398,
|
"const_condition": "1",
|
"having_condition": "having_field < 2",
|
"filesort": {
|
"sort_key": "0.1 + A.some_field",
|
"r_loops": 1,
|
"r_total_time_ms": 0.0373,
|
"r_used_priority_queue": false,
|
"r_output_rows": 0,
|
"r_buffer_size": "210",
|
"temporary_table": {
|
"read_sorted_file": {
|
"r_rows": 3,
|
"filesort": {
|
"sort_key": "A.`id`",
|
"r_loops": 1,
|
"r_total_time_ms": 0.035,
|
"r_used_priority_queue": false,
|
"r_output_rows": 3,
|
"r_buffer_size": "234",
|
"table": {
|
"table_name": "A",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.0077,
|
"filtered": 100,
|
"r_filtered": 1
|
}
|
}
|
},
|
"table": {
|
"table_name": "B2A",
|
"access_type": "ref",
|
"possible_keys": ["author_id"],
|
"key": "author_id",
|
"key_length": "4",
|
"used_key_parts": ["author_id"],
|
"ref": ["test.A.id"],
|
"r_loops": 3,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0212,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(B2A.author_id = A.`id`)"
|
},
|
"table": {
|
"table_name": "B",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "3",
|
"used_key_parts": ["id"],
|
"ref": ["test.B2A.book_id"],
|
"r_loops": 4,
|
"rows": 1,
|
"r_rows": 0.75,
|
"r_total_time_ms": 0.0119,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(B.`id` = B2A.book_id and trigcond(B2A.book_id is not null))"
|
}
|
}
|
}
|
}
|
}
|
|
10.1 ANALYZE JSON
|
ANALYZE
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.6897,
|
"const_condition": "1",
|
"having_condition": "(having_field < 2)",
|
"filesort": {
|
"r_loops": 1,
|
"r_total_time_ms": 0.0493,
|
"r_used_priority_queue": false,
|
"r_output_rows": 1,
|
"r_buffer_size": "273",
|
"filesort": {
|
"r_loops": 1,
|
"r_total_time_ms": 0.053,
|
"r_used_priority_queue": false,
|
"r_output_rows": 3,
|
"r_buffer_size": "234",
|
"temporary_table": {
|
"table": {
|
"table_name": "A",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 3,
|
"r_rows": 3,
|
"r_total_time_ms": 0.0073,
|
"filtered": 100,
|
"r_filtered": 100
|
},
|
"table": {
|
"table_name": "B2A",
|
"access_type": "ref",
|
"possible_keys": ["author_id"],
|
"key": "author_id",
|
"key_length": "4",
|
"used_key_parts": ["author_id"],
|
"ref": ["test.A.id"],
|
"r_loops": 3,
|
"rows": 2,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0234,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond((B2A.author_id = A.`id`))"
|
},
|
"table": {
|
"table_name": "B",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "3",
|
"used_key_parts": ["id"],
|
"ref": ["test.B2A.book_id"],
|
"r_loops": 4,
|
"rows": 1,
|
"r_rows": 0.75,
|
"r_total_time_ms": 0.0137,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "trigcond(((B.`id` = B2A.book_id) and trigcond((B2A.book_id is not null))))"
|
}
|
}
|
}
|
}
|
}
|
}
|
|