diff options
Diffstat (limited to 'mysql-test/main/opt_trace_index_merge.result')
| -rw-r--r-- | mysql-test/main/opt_trace_index_merge.result | 149 |
1 files changed, 73 insertions, 76 deletions
diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index ee9ad1be2ba..a729ca9ea71 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -73,7 +73,7 @@ explain select * from t1 where a=1 or b=1 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 231.5878906 + "cost": 264.7939453 }, "potential_range_indexes": [ { @@ -111,12 +111,12 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.345585794, + "cost": 0.745585794, "chosen": true } ], "index_to_merge": "a", - "cumulated_cost": 0.345585794 + "cumulated_cost": 0.745585794 }, { "range_scan_alternatives": [ @@ -127,15 +127,15 @@ explain select * from t1 where a=1 or b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.345585794, + "cost": 0.745585794, "chosen": true } ], "index_to_merge": "b", - "cumulated_cost": 0.691171589 + "cumulated_cost": 1.491171589 } ], - "cost_of_reading_ranges": 0.691171589, + "cost_of_reading_ranges": 1.491171589, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": [ @@ -158,7 +158,7 @@ explain select * from t1 where a=1 or b=1 { } } ], - "index_roworder_union_cost": 2.484903732, + "index_roworder_union_cost": 2.595171589, "members": 2, "chosen": true } @@ -187,7 +187,7 @@ explain select * from t1 where a=1 or b=1 { ] }, "rows_for_plan": 2, - "cost_for_plan": 2.484903732, + "cost_for_plan": 2.595171589, "chosen": true } } @@ -209,23 +209,28 @@ explain select * from t1 where a=1 or b=1 { "plan_prefix": [], "table": "t1", "best_access_path": { + "plan_details": { + "record_count": 1 + }, "considered_access_paths": [ { "access_type": "index_merge", - "resulting_rows": 2, - "cost": 2.484903732, + "rows": 2, + "rows_after_scan": 2, + "rows_after_filter": 2, + "cost": 2.595171589, "chosen": true } ], "chosen_access_method": { "type": "index_merge", "records": 2, - "cost": 2.484903732, + "cost": 2.595171589, "uses_join_buffering": false } }, "rows_for_plan": 2, - "cost_for_plan": 2.884903732, + "cost_for_plan": 2.595171589, "estimated_join_cardinality": 2 } ] @@ -233,7 +238,8 @@ explain select * from t1 where a=1 or b=1 { { "best_join_order": ["t1"], "best_access_method": { - "cost": 2.883903732 + "rows": 2, + "cost": 2.595171589 } }, { @@ -315,7 +321,7 @@ set optimizer_trace='enabled=on'; # 3-way ROR-intersection explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index +1 SIMPLE t1 index_merge key1,key2,key3 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ @@ -334,7 +340,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2700.058937, + "cost": 1695.083937, "chosen": true }, @@ -348,7 +354,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2700.058937, + "cost": 1695.083937, "chosen": false, "cause": "cost" }, @@ -363,7 +369,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2243, - "cost": 2700.058937, + "cost": 1695.083937, "chosen": false, "cause": "cost" } @@ -375,10 +381,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 10.31393703, - "disk_sweep_cost": 1923.144061, - "cumulative_total_cost": 1933.457998, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 61.88893703, + "disk_sweep_cost": 1682.25, + "cumulative_total_cost": 1744.138937, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -387,10 +393,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 20.62787405, - "disk_sweep_cost": 84.51771758, - "cumulative_total_cost": 105.1455916, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 123.7778741, + "disk_sweep_cost": 57.75, + "cumulative_total_cost": 181.5278741, "usable": true, "matching_rows_now": 77.6360508, "intersect_covering_with_this_index": false, @@ -399,14 +405,15 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 30.94181108, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 185.6668111, "disk_sweep_cost": 0, - "cumulative_total_cost": 30.94181108, + "cumulative_total_cost": 185.6668111, "usable": true, "matching_rows_now": 2.687185191, "intersect_covering_with_this_index": true, - "chosen": true + "chosen": false, + "cause": "does not reduce cost" } ], "clustered_pk": @@ -414,9 +421,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "clustered_pk_added_to_intersect": false, "cause": "no clustered pk index" }, - "rows": 2, - "cost": 30.94181108, - "covering": true, + "rows": 77, + "cost": 197.0550842, + "covering": false, "chosen": true }, "analyzing_index_merge_union": @@ -432,9 +439,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) "range_access_plan": { "type": "index_roworder_intersect", - "rows": 2, - "cost": 30.94181108, - "covering": true, + "rows": 77, + "cost": 197.0550842, + "covering": false, "clustered_pk_scan": false, "intersect_of": [ @@ -457,21 +464,11 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) [ "(100) <= (key2) <= (100)" ] - }, - - { - "type": "range_scan", - "index": "key3", - "rows": 2243, - "ranges": - [ - "(100) <= (key3) <= (100)" - ] } ] }, - "rows_for_plan": 2, - "cost_for_plan": 30.94181108, + "rows_for_plan": 77, + "cost_for_plan": 197.0550842, "chosen": true } ] @@ -512,7 +509,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 457.058937, + "cost": 517.508937, "chosen": true }, @@ -526,13 +523,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 457.058937, + "cost": 517.508937, "chosen": false, "cause": "cost" } ], "index_to_merge": "key1", - "cumulated_cost": 457.058937 + "cumulated_cost": 517.508937 }, { @@ -549,7 +546,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 457.058937, + "cost": 517.508937, "chosen": true }, @@ -563,16 +560,16 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 2243, - "cost": 457.058937, + "cost": 517.508937, "chosen": false, "cause": "cost" } ], "index_to_merge": "key3", - "cumulated_cost": 914.1178741 + "cumulated_cost": 1035.017874 } ], - "cost_of_reading_ranges": 914.1178741, + "cost_of_reading_ranges": 1035.017874, "use_roworder_union": true, "cause": "always cheaper than non roworder retrieval", "analyzing_roworder_scans": @@ -593,10 +590,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key1", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 10.31393703, - "disk_sweep_cost": 1923.144061, - "cumulative_total_cost": 1933.457998, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 61.88893703, + "disk_sweep_cost": 1682.25, + "cumulative_total_cost": 1744.138937, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -605,10 +602,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key2", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 20.62787405, - "disk_sweep_cost": 84.51771758, - "cumulative_total_cost": 105.1455916, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 123.7778741, + "disk_sweep_cost": 57.75, + "cumulative_total_cost": 181.5278741, "usable": true, "matching_rows_now": 77.6360508, "intersect_covering_with_this_index": false, @@ -621,7 +618,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 105.1455916, + "cost": 197.0550842, "covering": false, "chosen": true } @@ -642,10 +639,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key3", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 10.31393703, - "disk_sweep_cost": 1923.144061, - "cumulative_total_cost": 1933.457998, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 61.88893703, + "disk_sweep_cost": 1682.25, + "cumulative_total_cost": 1744.138937, "usable": true, "matching_rows_now": 2243, "intersect_covering_with_this_index": false, @@ -654,10 +651,10 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) { "index": "key4", - "index_scan_cost": 10.31393703, - "cumulated_index_scan_cost": 20.62787405, - "disk_sweep_cost": 84.51771758, - "cumulative_total_cost": 105.1455916, + "index_scan_cost": 61.88893703, + "cumulated_index_scan_cost": 123.7778741, + "disk_sweep_cost": 57.75, + "cumulative_total_cost": 181.5278741, "usable": true, "matching_rows_now": 77.6360508, "intersect_covering_with_this_index": false, @@ -670,13 +667,13 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "cause": "no clustered pk index" }, "rows": 77, - "cost": 105.1455916, + "cost": 197.0550842, "covering": false, "chosen": true } } ], - "index_roworder_union_cost": 194.9771115, + "index_roworder_union_cost": 332.5637481, "members": 2, "chosen": true } @@ -697,7 +694,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 105.1455916, + "cost": 197.0550842, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -728,7 +725,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) { "type": "index_roworder_intersect", "rows": 77, - "cost": 105.1455916, + "cost": 197.0550842, "covering": false, "clustered_pk_scan": false, "intersect_of": @@ -758,7 +755,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.chosen_range_access_summary')) ] }, "rows_for_plan": 154, - "cost_for_plan": 194.9771115, + "cost_for_plan": 332.5637481, "chosen": true } ] |