diff options
Diffstat (limited to 'mysql-test/main/opt_trace.result')
| -rw-r--r-- | mysql-test/main/opt_trace.result | 3524 |
1 files changed, 2112 insertions, 1412 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index a32e392f8f7..837662adab6 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -118,8 +118,8 @@ select * from v1 { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01024881, + "read_and_compare_cost": 0.01031281 } } ] @@ -137,30 +137,30 @@ select * from v1 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01031281, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01031281, "uses_join_buffering": false } }, "rows_for_plan": 1, - "cost_for_plan": 1.502197266 + "cost_for_plan": 0.01031281 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 1.502197266 + "cost": 0.01031281 }, { "substitute_best_equal": { @@ -278,8 +278,8 @@ select * from (select * from t1 where t1.a=1)q { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01024881, + "read_and_compare_cost": 0.01031281 } } ] @@ -297,30 +297,30 @@ select * from (select * from t1 where t1.a=1)q { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01031281, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01031281, "uses_join_buffering": false } }, "rows_for_plan": 1, - "cost_for_plan": 1.502197266 + "cost_for_plan": 0.01031281 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 1.502197266 + "cost": 0.01031281 }, { "substitute_best_equal": { @@ -443,8 +443,8 @@ select * from v2 { "table": "t1", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01024881, + "read_and_compare_cost": 0.01031281 } } ] @@ -462,9 +462,9 @@ select * from v2 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 1.502197266, + "rows_out": 1, + "cost": 0.01031281, "index_only": false, "chosen": true, "use_tmp_table": true @@ -472,22 +472,22 @@ select * from v2 { ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 1, - "cost": 1.502197266, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01031281, "uses_join_buffering": false } }, "rows_for_plan": 1, - "cost_for_plan": 1.502197266, - "cost_for_sorting": 1 + "cost_for_plan": 0.01031281, + "cost_for_sorting": 6.301866e-4 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 2.502197266 + "cost": 0.010942997 }, { "substitute_best_equal": { @@ -525,8 +525,8 @@ select * from v2 { "table": "<derived2>", "table_scan": { "rows": 2, - "read_cost": 2, - "read_and_compare_cost": 2.5 + "read_cost": 0.012350033, + "read_and_compare_cost": 0.012418701 } } ] @@ -544,30 +544,30 @@ select * from v2 { { "access_type": "scan", "rows": 2, - "rows_after_scan": 2, "rows_after_filter": 2, - "cost": 2.5, + "rows_out": 2, + "cost": 0.012418701, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 2, - "records_out": 2, - "cost": 2.5, + "rows_read": 2, + "rows_out": 2, + "cost": 0.012418701, "uses_join_buffering": false } }, "rows_for_plan": 2, - "cost_for_plan": 2.5 + "cost_for_plan": 0.012418701 } ] }, { "best_join_order": ["<derived2>"], "rows": 2, - "cost": 2.5 + "cost": 0.012418701 }, { "attaching_conditions_to_tables": { @@ -671,8 +671,8 @@ explain select * from v2 { "table": "t2", "table_scan": { "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "read_cost": 0.01124405, + "read_and_compare_cost": 0.01156405 } } ] @@ -690,30 +690,30 @@ explain select * from v2 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01156405, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01156405, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 3.510986328 + "cost_for_plan": 0.01156405 } ] }, { "best_join_order": ["t2"], "rows": 10, - "cost": 3.510986328 + "cost": 0.01156405 }, { "attaching_conditions_to_tables": { @@ -796,8 +796,8 @@ explain select * from v1 { "table": "t1", "table_scan": { "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "read_cost": 0.01124405, + "read_and_compare_cost": 0.01156405 } } ] @@ -815,9 +815,9 @@ explain select * from v1 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01156405, "index_only": false, "chosen": true, "use_tmp_table": true @@ -825,22 +825,22 @@ explain select * from v1 { ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01156405, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 3.510986328, - "cost_for_sorting": 10 + "cost_for_plan": 0.01156405, + "cost_for_sorting": 0.006368384 } ] }, { "best_join_order": ["t1"], "rows": 10, - "cost": 13.51098633 + "cost": 0.017932434 }, { "attaching_conditions_to_tables": { @@ -872,8 +872,8 @@ explain select * from v1 { "table": "<derived2>", "table_scan": { "rows": 10, - "read_cost": 10, - "read_and_compare_cost": 12.5 + "read_cost": 0.012414166, + "read_and_compare_cost": 0.012757506 } } ] @@ -891,30 +891,30 @@ explain select * from v1 { { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 12.5, + "rows_out": 10, + "cost": 0.012757506, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 12.5, + "rows_read": 10, + "rows_out": 10, + "cost": 0.012757506, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 12.5 + "cost_for_plan": 0.012757506 } ] }, { "best_join_order": ["<derived2>"], "rows": 10, - "cost": 12.5 + "cost": 0.012757506 }, { "attaching_conditions_to_tables": { @@ -1044,16 +1044,16 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "table": "t1", "table_scan": { "rows": 100, - "read_cost": 1.158691406, - "read_and_compare_cost": 26.15869141 + "read_cost": 0.0224405, + "read_and_compare_cost": 0.0256405 } }, { "table": "t2", "table_scan": { "rows": 100, - "read_cost": 1.158691406, - "read_and_compare_cost": 26.15869141 + "read_cost": 0.0224405, + "read_and_compare_cost": 0.0256405 } } ] @@ -1071,23 +1071,23 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.15869141, + "rows_out": 100, + "cost": 0.0256405, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.15869141, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256405, "uses_join_buffering": false } }, "rows_for_plan": 100, - "cost_for_plan": 26.15869141, + "cost_for_plan": 0.0256405, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -1103,30 +1103,29 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 125.0585794, + "cost": 0.1731074, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 1807.408691, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9603871, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 125.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1731074, "uses_join_buffering": false } }, "rows_for_plan": 100, - "cost_for_plan": 151.2172709, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.1987479 } ] }, @@ -1141,23 +1140,23 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.15869141, + "rows_out": 100, + "cost": 0.0256405, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.15869141, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256405, "uses_join_buffering": false } }, "rows_for_plan": 100, - "cost_for_plan": 26.15869141, + "cost_for_plan": 0.0256405, "rest_of_plan": [ { "plan_prefix": ["t2"], @@ -1173,29 +1172,29 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 125.0585794, + "cost": 0.1731074, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 1807.408691, + "rows_after_filter": 100, + "rows_out": 1, + "cost": 0.9603871, "index_only": false, "chosen": false } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 125.0585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.1731074, "uses_join_buffering": false } }, "rows_for_plan": 100, - "cost_for_plan": 151.2172709, + "cost_for_plan": 0.1987479, "pruned_by_cost": true } ] @@ -1205,7 +1204,7 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "best_join_order": ["t1", "t2"], "rows": 100, - "cost": 151.2172709 + "cost": 0.1987479 }, { "substitute_best_equal": { @@ -1244,10 +1243,11 @@ drop table t1,t2,t0; # group_by min max optimization # CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a INT NOT NULL, KEY(a)); +insert into t1 select seq, mod(seq,4)+1 from seq_1_to_65536; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK +test.t1 analyze status Table is already up to date EXPLAIN SELECT DISTINCT a FROM t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range NULL a 4 NULL 5 Using index for group-by @@ -1286,7 +1286,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "range_analysis": { "table_scan": { "rows": 65536, - "cost": 16457 + "cost": 10.29477568 }, "potential_range_indexes": [ { @@ -1302,7 +1302,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "best_covering_index_scan": { "index": "a", - "cost": 14898.29141, + "cost": 9.123706862, "chosen": true }, "group_index_range": { @@ -1312,7 +1312,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "index": "a", "covering": true, "rows": 5, - "cost": 6.5 + "cost": 0.004191135 } ] }, @@ -1324,7 +1324,7 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.5, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [], "chosen": true @@ -1338,12 +1338,12 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "max_aggregate": false, "distinct_aggregate": false, "rows": 5, - "cost": 6.5, + "cost": 0.004191135, "key_parts_used_for_access": ["a"], "ranges": [] }, "rows_for_plan": 5, - "cost_for_plan": 6.5, + "cost_for_plan": 0.004191135, "chosen": true } } @@ -1363,29 +1363,29 @@ EXPLAIN SELECT DISTINCT a FROM t1 { { "access_type": "index_merge", "rows": 5, - "rows_after_scan": 5, "rows_after_filter": 5, - "cost": 6.5, + "rows_out": 5, + "cost": 0.004191135, "chosen": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 5, - "records_out": 5, - "cost": 6.5, + "rows_read": 5, + "rows_out": 5, + "cost": 0.004191135, "uses_join_buffering": false } }, "rows_for_plan": 5, - "cost_for_plan": 6.5 + "cost_for_plan": 0.004191135 } ] }, { "best_join_order": ["t1"], "rows": 5, - "cost": 6.5 + "cost": 0.004191135 }, { "attaching_conditions_to_tables": { @@ -1421,10 +1421,13 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range NULL a 20 NULL 7 Using where; Using index for group-by +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a; +id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 20 NULL 7 Using where; Using index select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { +set statement optimizer_scan_setup_cost=0 for EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "steps": [ { "join_preparation": { @@ -1480,7 +1483,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "range_analysis": { "table_scan": { "rows": 7, - "cost": 2.764526367 + "cost": 0.001094835 }, "potential_range_indexes": [ { @@ -1491,8 +1494,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "best_covering_index_scan": { "index": "a", - "cost": 2.084226263, - "chosen": true + "cost": 0.001758432, + "chosen": false, + "cause": "cost" }, "setup_range_conditions": [], "analyzing_range_alternatives": { @@ -1509,7 +1513,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "covering": true, "ranges": ["(2,3) <= (b,c) <= (2,3)"], "rows": 7, - "cost": 2.75 + "cost": 0.004425189 } ] }, @@ -1521,7 +1525,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "max_aggregate": false, "distinct_aggregate": false, "rows": 7, - "cost": 2.75, + "cost": 0.004425189, "key_parts_used_for_access": ["a", "b", "c"], "ranges": ["(2,3) <= (b,c) <= (2,3)"], "chosen": false, @@ -1560,9 +1564,9 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { { "access_type": "scan", "rows": 7, - "rows_after_scan": 1, "rows_after_filter": 1, - "cost": 2.084226263, + "rows_out": 0.584899902, + "cost": 0.001758432, "index_only": true, "chosen": true, "use_tmp_table": true @@ -1570,22 +1574,22 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "chosen_access_method": { "type": "scan", - "records_read": 1, - "records_out": 0.584899902, - "cost": 2.084226263, + "rows_read": 1, + "rows_out": 0.584899902, + "cost": 0.001758432, "uses_join_buffering": false } }, - "rows_for_plan": 1, - "cost_for_plan": 2.084226263, - "cost_for_sorting": 1 + "rows_for_plan": 0.584899902, + "cost_for_plan": 0.001758432, + "cost_for_sorting": 3.670142e-4 } ] }, { "best_join_order": ["t1"], - "rows": 1, - "cost": 3.084226263 + "rows": 0.584899902, + "cost": 0.002125446 }, { "substitute_best_equal": { @@ -1609,8 +1613,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "clause": "GROUP BY", "table": "t1", "rows_estimation": 1, - "read_cost": 2.147624763, - "filesort_cost": 0.0633985, + "read_cost": 0.001804223, + "filesort_cost": 4.579083e-5, "filesort_type": "priority_queue with addon fields", "fanout": 1, "possible_keys": [ @@ -1619,7 +1623,7 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { "can_resolve_order": true, "rows_to_examine": 7, "range_scan": false, - "scan_cost": 2.084226263, + "scan_cost": 0.001667847, "chosen": true } ] @@ -1708,7 +1712,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 5.015625 + "cost": 0.01250248 }, "potential_range_indexes": [ { @@ -1719,7 +1723,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.11171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -1737,7 +1741,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a)"], "rows": 9, - "cost": 3.25 + "cost": 0.005620843 } ] }, @@ -1749,7 +1753,7 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"], "chosen": true @@ -1763,12 +1767,12 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { "max_aggregate": true, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id"], "ranges": ["(2001-01-04) <= (a)"] }, "rows_for_plan": 9, - "cost_for_plan": 3.25, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -1788,31 +1792,31 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { { "access_type": "index_merge", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 9, - "records_out": 9, - "cost": 3.25, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { "best_join_order": ["t1"], "rows": 9, - "cost": 12.25 + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -1903,7 +1907,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "range_analysis": { "table_scan": { "rows": 16, - "cost": 5.015625 + "cost": 0.01250248 }, "potential_range_indexes": [ { @@ -1914,7 +1918,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "best_covering_index_scan": { "index": "id", - "cost": 4.11171589, + "cost": 0.008002862, "chosen": true }, "setup_range_conditions": [], @@ -1932,7 +1936,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "covering": true, "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "rows": 9, - "cost": 3.25 + "cost": 0.005620843 } ] }, @@ -1944,7 +1948,7 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"], "chosen": true @@ -1958,12 +1962,12 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { "max_aggregate": false, "distinct_aggregate": false, "rows": 9, - "cost": 3.25, + "cost": 0.005620843, "key_parts_used_for_access": ["id", "a"], "ranges": ["(2001-01-04) <= (a) <= (2001-01-04)"] }, "rows_for_plan": 9, - "cost_for_plan": 3.25, + "cost_for_plan": 0.005620843, "chosen": true } } @@ -1983,31 +1987,31 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { { "access_type": "index_merge", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25, + "rows_out": 9, + "cost": 0.005620843, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "index_merge", - "records_read": 9, - "records_out": 9, - "cost": 3.25, + "rows_read": 9, + "rows_out": 9, + "cost": 0.005620843, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25, - "cost_for_sorting": 9 + "cost_for_plan": 0.005620843, + "cost_for_sorting": 0.005728198 } ] }, { "best_join_order": ["t1"], "rows": 9, - "cost": 12.25 + "cost": 0.011349041 }, { "substitute_best_equal": { @@ -2041,10 +2045,6 @@ drop table t1; # # Late ORDER BY optimization # -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 primary key); -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 not null, a int, @@ -2055,19 +2055,18 @@ KEY c(c), KEY a_c(a,c), KEY a_b(a,b) ); -insert into t1 -select a, a,a,a, 'filler-dataaa' from test.one_k; +insert into t1 select seq, seq,seq,seq, 'filler-dataaa' from seq_0_to_999; update t1 set a=1 where pk between 0 and 180; update t1 set b=2 where pk between 0 and 20; analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK -set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref a_c,a_b a_b 10 const,const 21 Using where; Using filesort update t1 set b=2 where pk between 20 and 40; +set optimizer_trace='enabled=on'; explain select * from t1 where a=1 and b=2 order by c limit 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a_c,a_b a_c 5 NULL 180 Using where @@ -2148,7 +2147,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 265.2822266 + "cost": 0.1731362 }, "potential_range_indexes": [ { @@ -2177,8 +2176,9 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 135.6693776, - "chosen": true + "cost": 0.223346519, + "chosen": false, + "cause": "cost" }, { "index": "a_b", @@ -2187,7 +2187,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 41, - "cost": 31.3040249, + "cost": 0.051838728, "chosen": true } ], @@ -2208,7 +2208,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1,2) <= (a,b) <= (1,2)"] }, "rows_for_plan": 41, - "cost_for_plan": 31.3040249, + "cost_for_plan": 0.051838728, "chosen": true } } @@ -2218,12 +2218,12 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "rowid_filters": [ { "key": "a_b", - "build_cost": 1.752281351, + "build_cost": 0.006243462, "rows": 41 }, { "key": "a_c", - "build_cost": 6.264109827, + "build_cost": 0.026549953, "rows": 180 } ] @@ -2266,7 +2266,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_c", "used_range_estimates": true, "rows": 180, - "cost": 135.6493776, + "cost": 0.222796377, "chosen": true }, { @@ -2274,7 +2274,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "index": "a_b", "used_range_estimates": true, "rows": 41, - "cost": 31.2840249, + "cost": 0.051288586, "chosen": true }, { @@ -2285,21 +2285,21 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ], "chosen_access_method": { "type": "ref", - "records_read": 41, - "records_out": 41, - "cost": 31.2840249, + "rows_read": 41, + "rows_out": 41, + "cost": 0.051288586, "uses_join_buffering": false } }, "rows_for_plan": 41, - "cost_for_plan": 31.2840249 + "cost_for_plan": 0.051288586 } ] }, { "best_join_order": ["t1"], "rows": 41, - "cost": 31.2840249 + "cost": 0.051288586 }, { "substitute_best_equal": { @@ -2323,8 +2323,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "clause": "ORDER BY", "table": "t1", "rows_estimation": 41, - "read_cost": 32.58369415, - "filesort_cost": 1.299669251, + "read_cost": 0.052227298, + "filesort_cost": 9.387121e-4, "filesort_type": "priority_queue with addon fields", "fanout": 1, "possible_keys": [ @@ -2333,7 +2333,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "can_resolve_order": true, "rows_to_examine": 24, "range_scan": false, - "scan_cost": 18.51405907, + "scan_cost": 0.030312813, "chosen": true }, { @@ -2341,7 +2341,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "can_resolve_order": true, "rows_to_examine": 4, "range_scan": true, - "scan_cost": 10.5218905, + "scan_cost": 0.021302281, "chosen": true }, { @@ -2355,10 +2355,6 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { { "table": "t1", "range_analysis": { - "table_scan": { - "rows": 1000, - "cost": 1.79769e308 - }, "potential_range_indexes": [ { "index": "c", @@ -2386,7 +2382,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "using_mrr": false, "index_only": false, "rows": 180, - "cost": 135.6693776, + "cost": 0.223346519, + "cost_with_limit": 0.002483968, "chosen": true } ], @@ -2407,7 +2404,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "ranges": ["(1) <= (a) <= (1)"] }, "rows_for_plan": 180, - "cost_for_plan": 135.6693776, + "cost_for_plan": 0.223346519, "chosen": true } } @@ -2423,7 +2420,7 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { } ] } 0 0 -drop table t1,ten,one_k; +drop table t1; # # TABLE ELIMINATION # @@ -2513,8 +2510,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } }, { @@ -2538,30 +2535,30 @@ select t1.a from t1 left join t2 on t1.a=t2.a { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01062562 } ] }, { "best_join_order": ["t2", "t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01062562 }, { "substitute_best_equal": { @@ -2660,16 +2657,16 @@ explain select * from t1 left join t2 on t2.a=t1.a { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } }, { "table": "t2", "table_scan": { "rows": 2, - "read_cost": 1.002197266, - "read_and_compare_cost": 1.502197266 + "read_cost": 0.01024881, + "read_and_compare_cost": 0.01031281 } } ] @@ -2687,23 +2684,23 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 2.003417969, + "cost_for_plan": 0.01062562, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -2717,30 +2714,25 @@ explain select * from t1 left join t2 on t2.a=t1.a { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, - "cost": 5.002147913, + "cost": 0.007120904, "chosen": true }, { - "access_type": "scan", - "rows": 2, - "rows_after_scan": 1.5, - "rows_after_filter": 1.5, - "cost": 6.008789062, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", - "records_read": 1, - "records_out": 1, - "cost": 5.002147913, + "rows_read": 1, + "rows_out": 1, + "cost": 0.007120904, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 7.005565882, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.017746524 } ] } @@ -2749,7 +2741,7 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "best_join_order": ["t1", "t2"], "rows": 4, - "cost": 7.005565882 + "cost": 0.017746524 }, { "substitute_best_equal": { @@ -2882,8 +2874,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } }, { @@ -2913,30 +2905,30 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01062562 } ] }, { "best_join_order": ["t3", "t2", "t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01062562 }, { "substitute_best_equal": { @@ -2974,33 +2966,27 @@ drop table t0, t1, t2, t3; # # IN subquery to sem-join is traced # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1(a int, b int); -insert into t1 values (0,0),(1,1),(2,2); -create table t2 as select * from t1; -create table t11(a int, b int); -create table t10 (pk int, a int); -insert into t10 select a,a from t0; -create table t12 like t10; -insert into t12 select * from t10; -analyze table t1,t10; -Table Op Msg_type Msg_text -test.t1 analyze status Engine-independent statistics collected -test.t1 analyze status OK -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK +insert into t1 select seq,seq from seq_0_to_3; +create table t2 (p int, a int); +insert into t2 select seq,seq from seq_1_to_10; set optimizer_trace='enabled=on'; -explain extended select * from t1 where a in (select pk from t10); +explain extended select * from t1 where a in (select p from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` +insert into t2 select seq,seq from seq_10_to_100; +explain extended select * from t1 where a in (select p from t2); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 -2 MATERIALIZED t10 ALL NULL NULL NULL NULL 10 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t10`) where 1 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t2`) where `test`.`t2`.`p` = `test`.`t1`.`a` select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -explain extended select * from t1 where a in (select pk from t10) { +explain extended select * from t1 where a in (select p from t2) { "steps": [ { "join_preparation": { @@ -3028,13 +3014,13 @@ explain extended select * from t1 where a in (select pk from t10) { } }, { - "expanded_query": "/* select#2 */ select t10.pk from t10" + "expanded_query": "/* select#2 */ select t2.p from t2" } ] } }, { - "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t10.pk from t10)" + "expanded_query": "/* select#1 */ select t1.a AS a,t1.b AS b from t1 where t1.a in (/* select#2 */ select t2.p from t2)" } ] } @@ -3054,19 +3040,19 @@ explain extended select * from t1 where a in (select pk from t10) { { "condition_processing": { "condition": "WHERE", - "original_condition": "1 and t1.a = t10.pk", + "original_condition": "1 and t1.a = t2.p", "steps": [ { "transformation": "equality_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "constant_propagation", - "resulting_condition": "1 and multiple equal(t1.a, t10.pk)" + "resulting_condition": "1 and multiple equal(t1.a, t2.p)" }, { "transformation": "trivial_condition_removal", - "resulting_condition": "multiple equal(t1.a, t10.pk)" + "resulting_condition": "multiple equal(t1.a, t2.p)" } ] } @@ -3080,7 +3066,7 @@ explain extended select * from t1 where a in (select pk from t10) { "depends_on_map_bits": [] }, { - "table": "t10", + "table": "t2", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] @@ -3095,17 +3081,17 @@ explain extended select * from t1 where a in (select pk from t10) { { "table": "t1", "table_scan": { - "rows": 3, - "read_cost": 1.003295898, - "read_and_compare_cost": 1.753295898 + "rows": 4, + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } }, { - "table": "t10", + "table": "t2", "table_scan": { - "rows": 10, - "read_cost": 1.010986328, - "read_and_compare_cost": 3.510986328 + "rows": 101, + "read_cost": 0.022564905, + "read_and_compare_cost": 0.025796905 } } ] @@ -3122,7 +3108,7 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_execution_plans": [ { "plan_prefix": [], - "table": "t10", + "table": "t2", "best_access_path": { "plan_details": { "record_count": 1 @@ -3130,24 +3116,24 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_access_paths": [ { "access_type": "scan", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 3.510986328, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025796905, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025796905, "uses_join_buffering": false } }, - "rows_for_plan": 10, - "cost_for_plan": 3.510986328 + "rows_for_plan": 101, + "cost_for_plan": 0.025796905 } ] } @@ -3166,83 +3152,88 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_access_paths": [ { "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 1.753295898, + "rows": 4, + "rows_after_filter": 4, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.753295898, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, - "rows_for_plan": 3, - "cost_for_plan": 1.753295898, + "rows_for_plan": 4, + "cost_for_plan": 0.01062562, "semijoin_strategy_choice": [], "rest_of_plan": [ { "plan_prefix": ["t1"], - "table": "t10", + "table": "t2", "best_access_path": { "plan_details": { - "record_count": 3 + "record_count": 4 }, "considered_access_paths": [ { "access_type": "scan_with_join_cache", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 10.63598633, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.063558233, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 10.63598633, + "rows_read": 101, + "rows_out": 101, + "cost": 0.063558233, "uses_join_buffering": true } }, - "rows_for_plan": 30, - "cost_for_plan": 12.38928223, + "rows_for_plan": 404, + "cost_for_plan": 0.074183853, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 12.38928223 + "rows": 4, + "cost": 0.074183853 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 8.664282227 + "rows": 4, + "cost": 0.078697445 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 12.38928223, - "write_cost": 2.5, - "full_lookup_cost": 15, - "total_cost": 29.88928223 + "prefix_record_count": 4, + "tmp_table_records": 1, + "sj_inner_fanout": 101, + "rows": 4, + "dups_cost": 0.074183853, + "write_cost": 0.02564388, + "full_lookup_cost": 0.06503188, + "total_cost": 0.164859613 }, { - "chosen_strategy": "SJ-Materialization" + "chosen_strategy": "FirstMatch" } - ] + ], + "rows_out": 1, + "rows_for_plan": 4 } ] }, { "plan_prefix": [], - "table": "t10", + "table": "t2", "best_access_path": { "plan_details": { "record_count": 1 @@ -3250,24 +3241,24 @@ explain extended select * from t1 where a in (select pk from t10) { "considered_access_paths": [ { "access_type": "scan", - "rows": 10, - "rows_after_scan": 10, - "rows_after_filter": 10, - "cost": 3.510986328, + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.025796905, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 101, + "rows_out": 101, + "cost": 0.025796905, "uses_join_buffering": false } }, - "rows_for_plan": 10, - "cost_for_plan": 3.510986328, + "rows_for_plan": 101, + "cost_for_plan": 0.025796905, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -3276,31 +3267,50 @@ explain extended select * from t1 where a in (select pk from t10) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "SJ-Materialization", + "semi_join_strategy": "FirstMatch", "join_order": [ { - "table": "t10" + "table": "t2", + "best_access_path": { + "plan_details": { + "record_count": 4 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 101, + "rows_after_filter": 101, + "rows_out": 101, + "cost": 0.10318762, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 101, + "rows_out": 101, + "cost": 0.10318762, + "uses_join_buffering": false + } + } } ] } ] }, { - "best_join_order": ["t1", "<subquery2>"], - "rows": 3, - "cost": 8.664282227 + "best_join_order": ["t1", "t2"], + "rows": 4, + "cost": 0.074183853 }, { "substitute_best_equal": { "condition": "WHERE", - "resulting_condition": "1" + "resulting_condition": "t2.p = t1.a" } }, { - "condition_on_constant_tables": "1", - "computing_condition": [] - }, - { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ @@ -3309,12 +3319,8 @@ explain extended select * from t1 where a in (select pk from t10) { "attached": null }, { - "table": "t10", - "attached": null - }, - { - "table": "<subquery2>", - "attached": null + "table": "t2", + "attached": "t2.p = t1.a" } ] } @@ -3330,12 +3336,10 @@ explain extended select * from t1 where a in (select pk from t10) { } ] } 0 0 -drop table t0,t1,t11,t10,t12,t2; +drop table t1,t2; # # Selectivities for columns and indexes. # -create table t0 (a int); -insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 ( pk int, a int, @@ -3343,7 +3347,7 @@ b int, key pk(pk), key pk_a(pk,a), key pk_a_b(pk,a,b)); -insert into t1 select a,a,a from t0; +insert into t1 select seq,seq,seq from seq_0_to_9; ANALYZE TABLE t1 PERSISTENT FOR COLUMNS (a,b) INDEXES (); Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected @@ -3451,7 +3455,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.515869141 + "cost": 0.01156405 }, "potential_range_indexes": [ { @@ -3472,7 +3476,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "best_covering_index_scan": { "index": "pk_a_b", - "cost": 2.760739566, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -3485,7 +3489,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270585794, + "cost": 0.002483968, "chosen": true }, { @@ -3495,7 +3499,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270829876, + "cost": 0.002483968, "chosen": false, "cause": "cost" }, @@ -3506,7 +3510,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.746073957, + "cost": 0.001388369, "chosen": true } ], @@ -3514,10 +3518,10 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "intersecting_indexes": [ { "index": "pk", - "index_scan_cost": 0.525585794, - "cumulated_index_scan_cost": 0.525585794, - "disk_sweep_cost": 0.75, - "cumulative_total_cost": 1.275585794, + "index_scan_cost": 0.000806227, + "cumulated_index_scan_cost": 0.000806227, + "disk_sweep_cost": 0.001143284, + "cumulative_total_cost": 0.001949511, "usable": true, "matching_rows_now": 1, "intersect_covering_with_this_index": false, @@ -3555,7 +3559,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "ranges": ["(2,5,1) <= (pk,a,b) <= (2,5,1)"] }, "rows_for_plan": 1, - "cost_for_plan": 0.746073957, + "cost_for_plan": 0.001388369, "chosen": true } } @@ -3565,17 +3569,17 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "rowid_filters": [ { "key": "pk", - "build_cost": 0.526585794, + "build_cost": 8.140695e-4, "rows": 1 }, { "key": "pk_a", - "build_cost": 0.526829876, + "build_cost": 8.140695e-4, "rows": 1 }, { "key": "pk_a_b", - "build_cost": 0.527073957, + "build_cost": 8.140695e-4, "rows": 1 } ] @@ -3618,7 +3622,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk", "used_range_estimates": true, "rows": 1, - "cost": 1.250585794, + "cost": 0.001933826, "chosen": true }, { @@ -3626,7 +3630,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a", "used_range_estimates": true, "rows": 1, - "cost": 1.250829876, + "cost": 0.001933826, "chosen": false, "cause": "cost" }, @@ -3635,7 +3639,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "index": "pk_a_b", "used_range_estimates": true, "rows": 1, - "cost": 0.726073957, + "cost": 0.000838227, "chosen": true }, { @@ -3646,22 +3650,21 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 0.726073957, + "rows_read": 1, + "rows_out": 1, + "cost": 0.000838227, "uses_join_buffering": false } }, "rows_for_plan": 1, - "cost_for_plan": 0.726073957, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.000838227 } ] }, { "best_join_order": ["t1"], "rows": 1, - "cost": 0.726073957 + "cost": 0.000838227 }, { "substitute_best_equal": { @@ -3693,7 +3696,7 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { } 0 0 set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables= @save_use_stat_tables; -drop table t0,t1; +drop table t1; set optimizer_trace="enabled=off"; # # Tests added to show that sub-statements are not traced @@ -3757,8 +3760,8 @@ select f1(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } } ] @@ -3776,30 +3779,30 @@ select f1(a) from t1 { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01062562 } ] }, { "best_join_order": ["t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01062562 }, { "attaching_conditions_to_tables": { @@ -3863,8 +3866,8 @@ select f2(a) from t1 { "table": "t1", "table_scan": { "rows": 4, - "read_cost": 1.003417969, - "read_and_compare_cost": 2.003417969 + "read_cost": 0.01049762, + "read_and_compare_cost": 0.01062562 } } ] @@ -3882,30 +3885,30 @@ select f2(a) from t1 { { "access_type": "scan", "rows": 4, - "rows_after_scan": 4, "rows_after_filter": 4, - "cost": 2.003417969, + "rows_out": 4, + "cost": 0.01062562, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 4, - "records_out": 4, - "cost": 2.003417969, + "rows_read": 4, + "rows_out": 4, + "cost": 0.01062562, "uses_join_buffering": false } }, "rows_for_plan": 4, - "cost_for_plan": 2.003417969 + "cost_for_plan": 0.01062562 } ] }, { "best_join_order": ["t1"], "rows": 4, - "cost": 2.003417969 + "cost": 0.01062562 }, { "attaching_conditions_to_tables": { @@ -3946,7 +3949,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2470 +2451 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3960,7 +3963,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2370 0 + 2351 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3968,7 +3971,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2470 0 +select * from t1 2451 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3993,7 +3996,7 @@ explain delete from t0 where t0.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01156405 }, "potential_range_indexes": [ { @@ -4012,7 +4015,7 @@ explain delete from t0 where t0.a<3 { "using_mrr": false, "index_only": false, "rows": 3, - "cost": 2.771757383, + "cost": 0.004951706, "chosen": true } ], @@ -4030,7 +4033,7 @@ explain delete from t0 where t0.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 2.771757383, + "cost_for_plan": 0.004951706, "chosen": true } } @@ -4131,7 +4134,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01156405 }, "potential_range_indexes": [ { @@ -4142,7 +4145,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 2.755857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4155,7 +4158,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 1.196757383, + "cost": 0.001664909, "chosen": true } ], @@ -4176,7 +4179,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "chosen": true } } @@ -4196,7 +4199,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "range_analysis": { "table_scan": { "rows": 10, - "cost": 3.510986328 + "cost": 0.01156405 }, "potential_range_indexes": [ { @@ -4207,7 +4210,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "best_covering_index_scan": { "index": "a", - "cost": 2.755857945, + "cost": 0.007173242, "chosen": true }, "setup_range_conditions": [], @@ -4220,7 +4223,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "using_mrr": false, "index_only": true, "rows": 3, - "cost": 1.196757383, + "cost": 0.001664909, "chosen": true } ], @@ -4241,7 +4244,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "ranges": ["(NULL) < (a) < (3)"] }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "chosen": true } } @@ -4271,22 +4274,22 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.196757383, + "rows_out": 3, + "cost": 0.001664909, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 3, - "records_out": 3, - "cost": 1.196757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001664909, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "rest_of_plan": [ { "plan_prefix": ["t0"], @@ -4302,7 +4305,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 1, - "cost": 2.176757383, + "cost": 0.002105081, "chosen": true }, { @@ -4313,15 +4316,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 2.176757383, + "rows_read": 1, + "rows_out": 1, + "cost": 0.002105081, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 3.373514767, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.00376999 } ] }, @@ -4336,22 +4338,22 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "access_type": "range", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.196757383, + "rows_out": 3, + "cost": 0.001664909, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 3, - "records_out": 3, - "cost": 1.196757383, + "rows_read": 3, + "rows_out": 3, + "cost": 0.001664909, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.196757383, + "cost_for_plan": 0.001664909, "rest_of_plan": [ { "plan_prefix": ["t1"], @@ -4368,7 +4370,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "used_range_estimates": false, "cause": "not better than ref estimates", "rows": 2, - "cost": 2.853514767, + "cost": 0.002519891, "chosen": true }, { @@ -4379,14 +4381,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records_read": 2, - "records_out": 1.166666667, - "cost": 2.853514767, + "rows_read": 2, + "rows_out": 2, + "cost": 0.002519891, "uses_join_buffering": false } }, "rows_for_plan": 6, - "cost_for_plan": 4.05027215, + "cost_for_plan": 0.0041848, "pruned_by_cost": true } ] @@ -4396,7 +4398,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "best_join_order": ["t0", "t1"], "rows": 3, - "cost": 3.373514767 + "cost": 0.00376999 }, { "substitute_best_equal": { @@ -4505,8 +4507,8 @@ explain select * from (select rand() from t1)q { "table": "t1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } } ] @@ -4524,30 +4526,30 @@ explain select * from (select rand() from t1)q { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477 + "cost_for_plan": 0.010469215 } ] }, { "best_join_order": ["t1"], "rows": 3, - "cost": 1.752563477 + "cost": 0.010469215 }, { "attaching_conditions_to_tables": { @@ -4579,8 +4581,8 @@ explain select * from (select rand() from t1)q { "table": "<derived2>", "table_scan": { "rows": 3, - "read_cost": 3, - "read_and_compare_cost": 3.75 + "read_cost": 0.01235805, + "read_and_compare_cost": 0.012461052 } } ] @@ -4598,30 +4600,30 @@ explain select * from (select rand() from t1)q { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.75, + "rows_out": 3, + "cost": 0.012461052, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.75, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012461052, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 3.75 + "cost_for_plan": 0.012461052 } ] }, { "best_join_order": ["<derived2>"], "rows": 3, - "cost": 3.75 + "cost": 0.012461052 }, { "attaching_conditions_to_tables": { @@ -4773,24 +4775,24 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ "table": "t1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_inner_2", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } } ] @@ -4816,23 +4818,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], @@ -4845,23 +4847,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953 + "cost_for_plan": 0.021956822 } ] }, @@ -4876,23 +4878,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "pruned_by_heuristic": true } ] @@ -4913,23 +4915,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4943,23 +4945,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -4973,46 +4975,51 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015167773, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015167773, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037124595, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 22.7833252 + "rows": 3, + "cost": 0.136099795 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 10.64519043 + "rows": 3, + "cost": 0.059481685 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 14.82019043, - "write_cost": 1.45, - "full_lookup_cost": 4.05, - "total_cost": 20.32019043 + "prefix_record_count": 3, + "tmp_table_records": 1, + "sj_inner_fanout": 9, + "rows": 3, + "dups_cost": 0.037124595, + "write_cost": 0.02548291, + "full_lookup_cost": 0.00434619, + "total_cost": 0.066953695 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "rows_out": 0.333333333, + "rows_for_plan": 3 } ] }, @@ -5027,23 +5034,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5060,23 +5067,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -5091,23 +5098,23 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5131,7 +5138,7 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_join_order": ["t1", "<subquery2>"], "rows": 3, - "cost": 10.64519043 + "cost": 0.059481685 }, { "substitute_best_equal": { @@ -5183,11 +5190,11 @@ explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t_outer_1 ALL NULL NULL NULL NULL 3 -1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 FirstMatch(t_outer_1); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_1 ALL NULL NULL NULL NULL 3 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t_inner_2 ALL NULL NULL NULL NULL 9 End temporary; Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Start temporary; Using join buffer (incremental, BNL join) 1 PRIMARY t_outer_2 ALL NULL NULL NULL NULL 9 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_4 ALL NULL NULL NULL NULL 3 Using join buffer (incremental, BNL join) -1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; FirstMatch(t_outer_2); Using join buffer (incremental, BNL join) +1 PRIMARY t_inner_3 ALL NULL NULL NULL NULL 9 Using where; End temporary; Using join buffer (incremental, BNL join) select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain select * from t1 t_outer_1,t2 t_outer_2 where t_outer_1.a in (select t_inner_1.a from t2 t_inner_2, t1 t_inner_1) and @@ -5346,48 +5353,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } } ] @@ -5420,23 +5427,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5450,23 +5457,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5480,41 +5487,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 36.32946777 + "rows": 3, + "cost": 0.144545665 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 31.17531738, - "write_cost": 1.45, - "full_lookup_cost": 12.15, - "total_cost": 44.77531738 + "prefix_record_count": 3, + "tmp_table_records": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046364553, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084886033 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } ], + "rows_out": 0.333333333, + "rows_for_plan": 3, "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -5527,23 +5539,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017384389, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017384389, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 48.0246582, + "cost_for_plan": 0.102270422, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5562,23 +5574,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034425181, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034425181, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 78.12722168, + "cost_for_plan": 0.136695603, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5598,41 +5610,46 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261522361, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 363.8724121, + "cost_for_plan": 0.398217964, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 359.2167969 + "rows": 27, + "cost": 1.308958472 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 363.8724121, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 478.2724121 + "prefix_record_count": 27, + "tmp_table_records": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.398217964, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.544911284 }, { - "chosen_strategy": "FirstMatch" + "chosen_strategy": "DuplicateWeedout" } - ] + ], + "rows_out": 0.333333333, + "rows_for_plan": 27 } ] }, @@ -5652,23 +5669,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.079988779, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.079988779, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 136.3323486, + "cost_for_plan": 0.182259201, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5685,23 +5702,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 4.565063477, + "rows_out": 3, + "cost": 0.012583195, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 4.565063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012583195, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 40.89453125, + "cost_for_plan": 0.097469228, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5720,23 +5737,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034268023, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034268023, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 72.50222168, + "cost_for_plan": 0.131737251, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5756,37 +5773,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261522361, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 358.2474121, + "cost_for_plan": 0.393259612, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 358.2474121, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 472.6474121 + "prefix_record_count": 3, + "tmp_table_records": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.393259612, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.539952932 }, { "chosen_strategy": "DuplicateWeedout" } ], - "pruned_by_cost": true + "rows_out": 0.333333333, + "rows_for_plan": 27 } ] }, @@ -5806,23 +5827,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034268023, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034268023, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 72.50222168, + "cost_for_plan": 0.131737251, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5839,23 +5860,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017384389, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017384389, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 48.0246582, + "cost_for_plan": 0.102270422, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -5872,23 +5893,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5902,37 +5923,175 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172779733, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219144286, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 262.2455078, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 376.6455078 + "prefix_record_count": 3, + "tmp_table_records": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219144286, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365837606 }, { "chosen_strategy": "DuplicateWeedout" } ], - "pruned_by_cost": true + "rows_out": 0.333333333, + "rows_for_plan": 27, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_4", + "best_access_path": { + "plan_details": { + "record_count": 27 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.034425181, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.034425181, + "uses_join_buffering": true + } + }, + "rows_for_plan": 81, + "cost_for_plan": 0.400262787, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261522361, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.661785148, + "semijoin_strategy_choice": [ + { + "strategy": "FirstMatch", + "rows": 27, + "cost": 1.572525656 + }, + { + "strategy": "DuplicateWeedout", + "prefix_record_count": 27, + "tmp_table_records": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.661785148, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.808478468 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rows_out": 0.333333333, + "rows_for_plan": 27, + "pruned_by_cost": true + } + ] + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "plan_details": { + "record_count": 27 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.079988779, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.079988779, + "uses_join_buffering": true + } + }, + "rows_for_plan": 243, + "cost_for_plan": 0.445826385, + "semijoin_strategy_choice": [], + "pruned_by_heuristic": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], @@ -5945,23 +6104,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067546675, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067546675, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 108.8653809, + "cost_for_plan": 0.113911228, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -5980,23 +6139,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742549079, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6016,23 +6175,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742549079, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -6049,25 +6208,98 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172779733, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219144286, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 729 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 1.863098263, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 1.863098263, + "uses_join_buffering": true + } + }, + "rows_for_plan": 6561, + "cost_for_plan": 2.082242549, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_3" + ], + "table": "t_inner_4", + "best_access_path": { + "plan_details": { + "record_count": 729 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.657280297, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.657280297, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.876424583, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] } ] }, @@ -6082,23 +6314,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015167773, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015167773, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037124595, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6112,23 +6344,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6147,23 +6379,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 862.7230713, + "cost_for_plan": 0.730960787, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6183,23 +6415,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 862.7230713, + "cost_for_plan": 0.730960787, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -6216,23 +6448,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6247,23 +6479,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6280,23 +6512,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6310,23 +6542,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172779733, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219144286, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6345,23 +6577,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 2299.60769, + "rows_out": 9, + "cost": 1.863098263, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 2299.60769, + "rows_read": 9, + "rows_out": 9, + "cost": 1.863098263, "uses_join_buffering": true } }, "rows_for_plan": 6561, - "cost_for_plan": 2561.853198, + "cost_for_plan": 2.082242549, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6381,23 +6613,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 767.2025635, + "rows_out": 3, + "cost": 0.657280297, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 767.2025635, + "rows_read": 3, + "rows_out": 3, + "cost": 0.657280297, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 1029.448071, + "cost_for_plan": 0.876424583, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -6414,23 +6646,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 231.0701904, + "rows_out": 9, + "cost": 0.172779733, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 231.0701904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 262.2455078, + "cost_for_plan": 0.219144286, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6445,23 +6677,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 77.69006348, + "rows_out": 3, + "cost": 0.067546675, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 77.69006348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.067546675, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 108.8653809, + "cost_for_plan": 0.113911228, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -6480,23 +6712,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742549079, "semijoin_strategy_choice": [], "pruned_by_cost": true }, @@ -6516,23 +6748,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 768.7076904, + "rows_out": 9, + "cost": 0.628637851, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 768.7076904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, "uses_join_buffering": true } }, "rows_for_plan": 2187, - "cost_for_plan": 877.5730713, + "cost_for_plan": 0.742549079, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -6553,23 +6785,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6584,23 +6816,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6615,23 +6847,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6646,23 +6878,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6679,23 +6911,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6710,23 +6942,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6741,23 +6973,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6772,23 +7004,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -6803,23 +7035,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -6828,118 +7060,10 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "fix_semijoin_strategies_for_picked_join_order": [ { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_4", - "best_access_path": { - "plan_details": { - "record_count": 27 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 47.31921387, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 47.31921387, - "uses_join_buffering": false - } - } - }, - { - "table": "t_inner_3", - "best_access_path": { - "plan_details": { - "record_count": 81 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 9, - "rows_after_scan": 9, - "rows_after_filter": 9, - "cost": 263.8729248, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 263.8729248, - "uses_join_buffering": false - } - } - } - ] + "semi_join_strategy": "DuplicateWeedout" }, { - "semi_join_strategy": "FirstMatch", - "join_order": [ - { - "table": "t_inner_1", - "best_access_path": { - "plan_details": { - "record_count": 3 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 3, - "rows_after_scan": 3, - "rows_after_filter": 3, - "cost": 5.25769043, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 5.25769043, - "uses_join_buffering": false - } - } - }, - { - "table": "t_inner_2", - "best_access_path": { - "plan_details": { - "record_count": 9 - }, - "considered_access_paths": [ - { - "access_type": "scan", - "rows": 9, - "rows_after_scan": 9, - "rows_after_filter": 9, - "cost": 29.31921387, - "index_only": false, - "chosen": true - } - ], - "chosen_access_method": { - "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 29.31921387, - "uses_join_buffering": false - } - } - } - ] + "semi_join_strategy": "DuplicateWeedout" } ] }, @@ -6948,12 +7072,12 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_outer_1", "t_inner_1", "t_inner_2", - "t_outer_2", "t_inner_4", + "t_outer_2", "t_inner_3" ], "rows": 27, - "cost": 359.2167969 + "cost": 0.539952932 }, { "substitute_best_equal": { @@ -6978,11 +7102,11 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "attached": null }, { - "table": "t_outer_2", + "table": "t_inner_4", "attached": null }, { - "table": "t_inner_4", + "table": "t_outer_2", "attached": null }, { @@ -7173,48 +7297,48 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "table": "t_outer_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_outer_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_2", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_1", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { "table": "t_inner_3", "table_scan": { "rows": 9, - "read_cost": 1.00769043, - "read_and_compare_cost": 3.25769043 + "read_cost": 0.011119645, + "read_and_compare_cost": 0.011407645 } }, { "table": "t_inner_4", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } } ] @@ -7245,23 +7369,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "rest_of_plan": [ { "plan_prefix": ["t_inner_1"], @@ -7274,23 +7398,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391 + "cost_for_plan": 0.02456684 } ] }, @@ -7305,23 +7429,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "pruned_by_heuristic": true } ] @@ -7339,23 +7463,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "rest_of_plan": [ { "plan_prefix": ["t_inner_4"], @@ -7368,23 +7492,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391 + "cost_for_plan": 0.02456684 } ] }, @@ -7399,23 +7523,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "pruned_by_heuristic": true } ] @@ -7436,23 +7560,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7466,23 +7590,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7496,46 +7620,51 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 3, - "cost": 36.32946777 + "rows": 3, + "cost": 0.144545665 }, { "strategy": "SJ-Materialization", - "records": 3, - "cost": 19.12531738 + "rows": 3, + "cost": 0.065031175 }, { "strategy": "DuplicateWeedout", - "records": 3, - "dups_cost": 31.17531738, - "write_cost": 1.45, - "full_lookup_cost": 12.15, - "total_cost": 44.77531738 + "prefix_record_count": 3, + "tmp_table_records": 1, + "sj_inner_fanout": 27, + "rows": 3, + "dups_cost": 0.046364553, + "write_cost": 0.02548291, + "full_lookup_cost": 0.01303857, + "total_cost": 0.084886033 }, { "chosen_strategy": "SJ-Materialization" } ], + "rows_out": 0.333333333, + "rows_for_plan": 3, "rest_of_plan": [ { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_2"], @@ -7548,23 +7677,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017384389, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017384389, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 30.82050781, + "cost_for_plan": 0.082415564, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7583,23 +7712,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 30.10256348, + "rows_out": 3, + "cost": 0.034425181, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 30.10256348, + "rows_read": 3, + "rows_out": 3, + "cost": 0.034425181, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 60.92307129, + "cost_for_plan": 0.116840745, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7619,46 +7748,51 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261522361, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 346.6682617, + "cost_for_plan": 0.378363106, "semijoin_strategy_choice": [ { "strategy": "FirstMatch", - "records": 27, - "cost": 342.0126465 + "rows": 27, + "cost": 1.289103614 }, { "strategy": "SJ-Materialization", - "records": 27, - "cost": 55.39326172 + "rows": 27, + "cost": 0.14166482 }, { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 346.6682617, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 461.0682617 + "prefix_record_count": 27, + "tmp_table_records": 1, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.378363106, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.525056426 }, { "chosen_strategy": "SJ-Materialization" } - ] + ], + "rows_out": 0.333333333, + "rows_for_plan": 27 } ] }, @@ -7678,23 +7812,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 88.30769043, + "rows_out": 9, + "cost": 0.079988779, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 88.30769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.079988779, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 119.1281982, + "cost_for_plan": 0.162404343, "semijoin_strategy_choice": [], "pruned_by_cost": true } @@ -7711,23 +7845,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 4.565063477, + "rows_out": 3, + "cost": 0.012583195, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 4.565063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.012583195, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 23.69038086, + "cost_for_plan": 0.07761437, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7746,23 +7880,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034268023, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034268023, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 55.29807129, + "cost_for_plan": 0.111882393, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7782,36 +7916,41 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 285.7451904, + "rows_out": 9, + "cost": 0.261522361, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 285.7451904, + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, "uses_join_buffering": true } }, "rows_for_plan": 729, - "cost_for_plan": 341.0432617, + "cost_for_plan": 0.373404754, "semijoin_strategy_choice": [ { "strategy": "DuplicateWeedout", - "records": 27, - "dups_cost": 341.0432617, - "write_cost": 5.05, - "full_lookup_cost": 109.35, - "total_cost": 455.4432617 + "prefix_record_count": 3, + "tmp_table_records": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.373404754, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.520098074 }, { "chosen_strategy": "DuplicateWeedout" } ], + "rows_out": 0.333333333, + "rows_for_plan": 27, "pruned_by_cost": true } ] @@ -7832,23 +7971,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 31.60769043, + "rows_out": 9, + "cost": 0.034268023, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 31.60769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.034268023, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 55.29807129, + "cost_for_plan": 0.111882393, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7865,23 +8004,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 11.69519043, + "rows_out": 9, + "cost": 0.017384389, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 11.69519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.017384389, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 30.82050781, + "cost_for_plan": 0.082415564, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -7898,25 +8037,275 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172779733, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.219144286, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_record_count": 3, + "tmp_table_records": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 0.219144286, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 0.365837606 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rows_out": 0.333333333, + "rows_for_plan": 27, + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_4", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067546675, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.067546675, + "uses_join_buffering": true + } + }, + "rows_for_plan": 243, + "cost_for_plan": 0.113911228, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.742549079, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4" + ], + "table": "t_inner_3", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.742549079, + "semijoin_strategy_choice": [ + { + "strategy": "SJ-Materialization", + "rows": 81, + "cost": 0.116160225 + }, + { + "chosen_strategy": "SJ-Materialization" + } + ], + "rows_out": 0.333333333, + "rows_for_plan": 81, + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_outer_2", + "t_inner_4", + "t_inner_3" + ], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.261522361, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.261522361, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.377682586, + "semijoin_strategy_choice": [ + { + "strategy": "DuplicateWeedout", + "prefix_record_count": 3, + "tmp_table_records": 9, + "sj_inner_fanout": 27, + "rows": 27, + "dups_cost": 1.00407144, + "write_cost": 0.02934619, + "full_lookup_cost": 0.11734713, + "total_cost": 1.15076476 + }, + { + "chosen_strategy": "DuplicateWeedout" + } + ], + "rows_out": 0.333333333, + "rows_for_plan": 27, + "pruned_by_cost": true + } + ] + } + ] + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_outer_2"], + "table": "t_inner_3", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172779733, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.219144286, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1"], @@ -7929,23 +8318,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 9.177563477, + "rows_out": 3, + "cost": 0.015167773, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 9.177563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.015167773, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 14.82019043, + "cost_for_plan": 0.037124595, "semijoin_strategy_choice": [], "rest_of_plan": [ { @@ -7959,25 +8348,98 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_inner_2" + ], + "table": "t_outer_2", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.730960787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_4", + "t_inner_2" + ], + "table": "t_inner_3", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.730960787, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + } + ] }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], @@ -7990,25 +8452,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "pruned_by_heuristic": true }, { "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_4"], @@ -8021,25 +8483,25 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 79.19519043, + "rows_out": 9, + "cost": 0.065198341, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 79.19519043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.065198341, "uses_join_buffering": true } }, "rows_for_plan": 243, - "cost_for_plan": 94.01538086, + "cost_for_plan": 0.102322936, "semijoin_strategy_choice": [], - "pruned_by_cost": true + "pruned_by_heuristic": true } ] }, @@ -8054,25 +8516,203 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 25.53269043, + "rows_out": 9, + "cost": 0.024407731, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 25.53269043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.024407731, "uses_join_buffering": true } }, "rows_for_plan": 81, - "cost_for_plan": 31.17531738, + "cost_for_plan": 0.046364553, "semijoin_strategy_choice": [], - "pruned_by_heuristic": true + "rest_of_plan": [ + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172779733, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.219144286, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_outer_2", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.172779733, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.172779733, + "uses_join_buffering": true + } + }, + "rows_for_plan": 729, + "cost_for_plan": 0.219144286, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": ["t_outer_1", "t_inner_1", "t_inner_3"], + "table": "t_inner_4", + "best_access_path": { + "plan_details": { + "record_count": 81 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 3, + "rows_after_filter": 3, + "rows_out": 3, + "cost": 0.067546675, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 3, + "rows_out": 3, + "cost": 0.067546675, + "uses_join_buffering": true + } + }, + "rows_for_plan": 243, + "cost_for_plan": 0.113911228, + "semijoin_strategy_choice": [], + "rest_of_plan": [ + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_inner_4" + ], + "table": "t_inner_2", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.742549079, + "semijoin_strategy_choice": [], + "pruned_by_cost": true + }, + { + "plan_prefix": [ + "t_outer_1", + "t_inner_1", + "t_inner_3", + "t_inner_4" + ], + "table": "t_outer_2", + "best_access_path": { + "plan_details": { + "record_count": 243 + }, + "considered_access_paths": [ + { + "access_type": "scan_with_join_cache", + "rows": 9, + "rows_after_filter": 9, + "rows_out": 9, + "cost": 0.628637851, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "rows_read": 9, + "rows_out": 9, + "cost": 0.628637851, + "uses_join_buffering": true + } + }, + "rows_for_plan": 2187, + "cost_for_plan": 0.742549079, + "semijoin_strategy_choice": [ + { + "strategy": "SJ-Materialization-Scan", + "rows": 729, + "cost": 0.388014063 + }, + { + "chosen_strategy": "SJ-Materialization-Scan" + } + ], + "rows_out": 3, + "rows_for_plan": 729, + "pruned_by_cost": true + } + ] + } + ] } ] }, @@ -8087,23 +8727,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8118,23 +8758,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8149,23 +8789,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 3.890063477, + "rows_out": 3, + "cost": 0.011487607, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 3.890063477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.011487607, "uses_join_buffering": true } }, "rows_for_plan": 9, - "cost_for_plan": 5.642626953, + "cost_for_plan": 0.021956822, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8180,23 +8820,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan_with_join_cache", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 9.67019043, + "rows_out": 9, + "cost": 0.014097625, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 9.67019043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.014097625, "uses_join_buffering": true } }, "rows_for_plan": 27, - "cost_for_plan": 11.42275391, + "cost_for_plan": 0.02456684, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -8213,23 +8853,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8244,23 +8884,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8275,23 +8915,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8306,23 +8946,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "semijoin_strategy_choice": [], "pruned_by_heuristic": true }, @@ -8337,23 +8977,23 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { { "access_type": "scan", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 9, - "cost": 3.25769043, + "rows_out": 9, + "cost": 0.011407645, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 9, - "records_out": 9, - "cost": 3.25769043, + "rows_read": 9, + "rows_out": 9, + "cost": 0.011407645, "uses_join_buffering": false } }, "rows_for_plan": 9, - "cost_for_plan": 3.25769043, + "cost_for_plan": 0.011407645, "semijoin_strategy_choice": [], "pruned_by_heuristic": true } @@ -8393,7 +9033,7 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "<subquery3>" ], "rows": 27, - "cost": 55.39326172 + "cost": 0.14166482 }, { "substitute_best_equal": { @@ -8501,7 +9141,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.745829876, + "cost": 0.001388369, "chosen": true } ], @@ -8535,7 +9175,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": true, "rows": 107, - "cost": 24.68379668, + "cost": 0.016044989, "chosen": true } ], @@ -8572,7 +9212,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 756.2522431, + "cost": 1.235599899, "chosen": true } ], @@ -8617,7 +9257,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 4, - "cost": 3.523710032, + "cost": 0.006185575, "chosen": true } ], @@ -8656,7 +9296,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.271171589, + "cost": 0.002483968, "chosen": true } ], @@ -8690,7 +9330,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.271171589, + "cost": 0.002483968, "chosen": true } ], @@ -8732,7 +9372,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270927508, + "cost": 0.002483968, "chosen": true } ], @@ -8767,7 +9407,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270878692, + "cost": 0.002483968, "chosen": true } ], @@ -8802,7 +9442,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270927508, + "cost": 0.002483968, "chosen": true } ], @@ -8840,7 +9480,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.270878692, + "cost": 0.002483968, "chosen": true } ], @@ -8881,7 +9521,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.319255553, + "cost": 0.002483968, "chosen": true } ], @@ -8901,7 +9541,7 @@ INSERT INTO t1 VALUES (2, 'ab\n'); set optimizer_trace=1; EXPLAIN SELECT * FROM t1 WHERE b='ab\n'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL i_b NULL NULL NULL 2 Using where +1 SIMPLE t1 ref i_b i_b 13 const 2 Using index condition select JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) [ @@ -8920,9 +9560,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 2, - "cost": 2.021855016, - "chosen": false, - "cause": "cost" + "cost": 0.003717837, + "chosen": true } ], "analyzing_roworder_intersect": @@ -8977,7 +9616,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1000, - "cost": 756.2522431, + "cost": 1.235599899, "chosen": true } ], @@ -9046,9 +9685,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 5.9375, "rows_after_filter": 5.9375, - "cost": 3.508544922, + "rows_out": 5.9375, + "cost": 0.01156405, "index_only": false, "chosen": true } @@ -9056,14 +9695,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 5.9375, - "records_out": 5.9375, - "cost": 3.508544922, + "rows_read": 5.9375, + "rows_out": 5.9375, + "cost": 0.01156405, "uses_join_buffering": false } }, "rows_for_plan": 5.9375, - "cost_for_plan": 3.508544922, + "cost_for_plan": 0.01156405, "rest_of_plan": [ @@ -9085,9 +9724,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 804.6875, "rows_after_filter": 804.6875, - "cost": 1496.835632, + "rows_out": 804.6875, + "cost": 0.990884438, "index_only": false, "chosen": true } @@ -9095,14 +9734,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 804.6875, - "records_out": 804.6875, - "cost": 1496.835632, + "rows_read": 804.6875, + "rows_out": 804.6875, + "cost": 0.990884438, "uses_join_buffering": false } }, "rows_for_plan": 4777.832031, - "cost_for_plan": 1500.344177 + "cost_for_plan": 1.002448488 } ] }, @@ -9124,9 +9763,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 804.6875, "rows_after_filter": 804.6875, - "cost": 252.0986328, + "rows_out": 804.6875, + "cost": 0.1668858, "index_only": false, "chosen": true } @@ -9134,14 +9773,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 804.6875, - "records_out": 804.6875, - "cost": 252.0986328, + "rows_read": 804.6875, + "rows_out": 804.6875, + "cost": 0.1668858, "uses_join_buffering": false } }, "rows_for_plan": 804.6875, - "cost_for_plan": 252.0986328, + "cost_for_plan": 0.1668858, "pruned_by_heuristic": true } ] @@ -9175,9 +9814,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.508544922, + "rows_out": 10, + "cost": 0.01156405, "index_only": false, "chosen": true } @@ -9185,14 +9824,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.508544922, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01156405, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 3.508544922, + "cost_for_plan": 0.01156405, "rest_of_plan": [ @@ -9217,31 +9856,27 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 12.50585794, + "cost": 0.01810946, "chosen": true }, { - "access_type": "scan_with_join_cache", - "rows": 1000, - "rows_after_scan": 603.515625, - "rows_after_filter": 603.515625, - "cost": 1685.448242, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 12.50585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01810946, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 16.01440287, + "cost_for_plan": 0.02967351, "selectivity": 0.8046875, "estimated_join_cardinality": 8.046875 } @@ -9265,9 +9900,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 1000, - "rows_after_scan": 804.6875, "rows_after_filter": 804.6875, - "cost": 252.0986328, + "rows_out": 804.6875, + "cost": 0.1668858, "index_only": false, "chosen": true } @@ -9275,14 +9910,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 804.6875, - "records_out": 804.6875, - "cost": 252.0986328, + "rows_read": 804.6875, + "rows_out": 804.6875, + "cost": 0.1668858, "uses_join_buffering": false } }, "rows_for_plan": 804.6875, - "cost_for_plan": 252.0986328, + "cost_for_plan": 0.1668858, "pruned_by_cost": true } ] @@ -9316,7 +9951,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.analyzing_range_alternatives')) "using_mrr": false, "index_only": false, "rows": 1, - "cost": 1.282887479, + "cost": 0.002483968, "chosen": true } ], @@ -9342,9 +9977,9 @@ insert into t3 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); explain select * from t3 where (a,a) in (select t1.a, t2.a from t1, t2 where t1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 -1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where -1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t3.a 1 Using where select JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; JSON_DETAILED(JSON_EXTRACT(trace, '$**.semijoin_table_pullout')) [ @@ -9382,7 +10017,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) "using_mrr": false, "index_only": true, "rows": 1, - "cost": 0.745829876, + "cost": 0.001388369, "chosen": true } ] @@ -9423,9 +10058,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 10, - "rows_after_scan": 10, "rows_after_filter": 10, - "cost": 3.510986328, + "rows_out": 10, + "cost": 0.01156405, "index_only": false, "chosen": true } @@ -9433,14 +10068,14 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 10, - "records_out": 10, - "cost": 3.510986328, + "rows_read": 10, + "rows_out": 10, + "cost": 0.01156405, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 3.510986328, + "cost_for_plan": 0.01156405, "rest_of_plan": [ @@ -9465,33 +10100,28 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "used_range_estimates": false, "cause": "not available", "rows": 1, - "cost": 12.50585794, + "cost": 0.01749506, "chosen": true }, { - "access_type": "scan_with_join_cache", - "rows": 100, - "rows_after_scan": 75, - "rows_after_filter": 75, - "cost": 204.2348633, - "index_only": false, - "chosen": false + "type": "scan", + "chosen": false, + "cause": "cost" } ], "chosen_access_method": { "type": "ref", - "records_read": 1, - "records_out": 1, - "cost": 12.50585794, + "rows_read": 1, + "rows_out": 1, + "cost": 0.01749506, "uses_join_buffering": false } }, "rows_for_plan": 10, - "cost_for_plan": 16.01684427, - "cost_for_sorting": 10, - "pruned_by_hanging_leaf": true + "cost_for_plan": 0.02905911, + "cost_for_sorting": 0.006368384 } ] }, @@ -9513,9 +10143,9 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) { "access_type": "scan", "rows": 100, - "rows_after_scan": 100, "rows_after_filter": 100, - "cost": 26.10986328, + "rows_out": 100, + "cost": 0.0256405, "index_only": false, "chosen": true, "use_tmp_table": true @@ -9524,15 +10154,66 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records_read": 100, - "records_out": 100, - "cost": 26.10986328, + "rows_read": 100, + "rows_out": 100, + "cost": 0.0256405, "uses_join_buffering": false } }, "rows_for_plan": 100, - "cost_for_plan": 26.10986328, - "pruned_by_cost": true + "cost_for_plan": 0.0256405, + "rest_of_plan": + [ + + { + "plan_prefix": + [ + "t2" + ], + "table": "t1", + "best_access_path": + { + "plan_details": + { + "record_count": 100 + }, + "considered_access_paths": + [ + + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "cause": "not available", + "rows": 1, + "cost": 0.1731074, + "chosen": true + }, + + { + "access_type": "scan_with_join_cache", + "rows": 10, + "rows_after_filter": 10, + "rows_out": 1, + "cost": 0.11051665, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": + { + "type": "scan", + "rows_read": 10, + "rows_out": 1, + "cost": 0.11051665, + "uses_join_buffering": true + } + }, + "rows_for_plan": 100, + "cost_for_plan": 0.13615715, + "pruned_by_cost": true + } + ] } ] ] @@ -9550,7 +10231,7 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN EXTENDED SELECT * from t1 WHERE a between 1 and 5 and b <= 5; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 100 1.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 100 0.22 Using where Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` between 1 and 5 and `test`.`t1`.`b` <= 5 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.selectivity_for_columns')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; @@ -9647,7 +10328,7 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.range_scan_alternatives')) "using_mrr": false, "index_only": false, "rows": 0, - "cost": 0.52, + "cost": 0.001340684, "chosen": true } ] @@ -9749,8 +10430,8 @@ select count(*) from seq_1_to_10000000 { "table": "seq_1_to_10000000", "table_scan": { "rows": 10000000, - "read_cost": 5000000, - "read_and_compare_cost": 7250000 + "read_cost": 124.7880673, + "read_and_compare_cost": 444.7880673 } } ] @@ -9768,30 +10449,30 @@ select count(*) from seq_1_to_10000000 { { "access_type": "scan", "rows": 10000000, - "rows_after_scan": 10000000, "rows_after_filter": 10000000, - "cost": 7250000, + "rows_out": 10000000, + "cost": 444.7880673, "index_only": true, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 10000000, - "records_out": 10000000, - "cost": 7250000, + "rows_read": 10000000, + "rows_out": 10000000, + "cost": 444.7880673, "uses_join_buffering": false } }, "rows_for_plan": 10000000, - "cost_for_plan": 7250000 + "cost_for_plan": 444.7880673 } ] }, { "best_join_order": ["seq_1_to_10000000"], "rows": 10000000, - "cost": 7250000 + "cost": 444.7880673 }, { "attaching_conditions_to_tables": { @@ -9830,7 +10511,7 @@ explain select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition -1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 const PRIMARY NULL NULL NULL 0 Impossible ON condition 1 SIMPLE t1 ALL NULL NULL NULL NULL 10 select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) from information_schema.optimizer_trace; @@ -10183,7 +10864,7 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "used_range_estimates": false, "cause": "not available", "rows": 1.8367, - "cost": 1.417925794, + "cost": 0.0019606, "chosen": true }, @@ -10196,27 +10877,33 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "chosen_access_method": { "type": "ref", - "records_read": 1.8367, - "records_out": 1.8367, - "cost": 1.417925794, + "rows_read": 1.8367, + "rows_out": 1.8367, + "cost": 0.0019606, "uses_join_buffering": false } }, "rows_for_plan": 1.8367, - "cost_for_plan": 1.417925794, - "cost_for_sorting": 1.8367 + "cost_for_plan": 0.0019606, + "cost_for_sorting": 0.001155201 } ] }, { - "best_splitting": + "split_materialized": { "table": "t2", "key": "idx_a", - "record_count": 4, - "cost": 4.157170953, - "unsplit_cost": 60.1794762 + "org_cost": 0.0019606, + "postjoin_cost": 0.001135418, + "one_splitting_cost": 0.003096018, + "unsplit_postjoin_cost": 0.036032575, + "unsplit_cost": 0.060589825, + "rows": 1.8367, + "outer_rows": 4, + "total_splitting_cost": 0.012384072, + "chosen": true } } ] @@ -10226,14 +10913,7 @@ json_detailed(json_extract(trace, '$**.lateral_derived')) from information_schema.optimizer_trace; json_detailed(json_extract(trace, '$**.lateral_derived')) -[ - - { - "startup_cost": 16.62868381, - "splitting_cost": 4.157170953, - "records": 1 - } -] +NULL drop table t1,t2; # # Test table functions. @@ -10290,7 +10970,6 @@ set @save_histogram_size= @@histogram_size; set @save_optimizer_use_condition_selectivity= @@optimizer_use_condition_selectivity; set optimizer_switch='rowid_filter=on'; set use_stat_tables='preferably'; -set optimizer_use_condition_selectivity=4; set histogram_size=127; create table t1 (a int, b int, c int, key(a),key(b)); insert into t1 select seq, seq*2, seq/10 from seq_1_to_1000; @@ -10358,7 +11037,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "range_analysis": { "table_scan": { "rows": 1000, - "cost": 252.5869141 + "cost": 0.1671262 }, "potential_range_indexes": [ { @@ -10382,7 +11061,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": false, "rows": 9, - "cost": 7.27527215, + "cost": 0.01235492, "chosen": true }, { @@ -10392,7 +11071,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": false, "rows": 21, - "cost": 16.28230168, + "cost": 0.027161348, "chosen": false, "cause": "cost" } @@ -10414,7 +11093,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "ranges": ["(NULL) < (a) < (10)"] }, "rows_for_plan": 9, - "cost_for_plan": 7.27527215, + "cost_for_plan": 0.01235492, "chosen": true } } @@ -10424,12 +11103,12 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "rowid_filters": [ { "key": "a", - "build_cost": 0.759047172, + "build_cost": 0.001839302, "rows": 9 }, { "key": "b", - "build_cost": 1.122236655, + "build_cost": 0.003459502, "rows": 21 } ] @@ -10469,51 +11148,49 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { { "filter": { "rowid_filter_key": "b", - "index_only_cost": 0.50527215, - "filter_startup_cost": 1.122236655, - "find_key_and_filter_lookup_cost": 0.386507019, + "index_only_cost": 0.001515222, + "filter_startup_cost": 0.003459502, + "find_key_and_filter_lookup_cost": 7.275007e-4, "filter_selectivity": 0.021, "orginal_rows": 9, "new_rows": 0.189, - "original_found_rows_cost": 5.45527215, - "new_found_rows_cost": 1.381168455, - "cost": 2.54120511, + "orginal_access_cost": 0.011516778, + "with_filter_access_cost": 0.002452755, + "original_found_rows_cost": 0.010001556, + "with_filter_found_rows_cost": 2.100327e-4, + "cost": 0.005918305, "filter_used": true }, "access_type": "range", "rows": 9, - "rows_after_scan": 9, "rows_after_filter": 0.189, - "cost": 2.54120511, + "rows_out": 0.020671875, + "cost": 0.005918305, "chosen": true } ], "chosen_access_method": { "type": "range", - "records_read": 9, - "records_out": 0.020671875, - "cost": 2.54120511, + "rows_read": 0.189, + "rows_out": 0.020671875, + "cost": 0.005918305, "uses_join_buffering": false, "rowid_filter_key": "b" } }, - "rows_for_plan": 9, - "cost_for_plan": 2.54120511 + "rows_for_plan": 0.020671875, + "cost_for_plan": 0.005918305 } ] }, { "best_join_order": ["t1"], - "rows": 9, - "cost": 2.54120511 + "rows": 0.020671875, + "cost": 0.005918305 }, { "table": "t1", "range_analysis": { - "table_scan": { - "rows": 1000, - "cost": 1.79769e308 - }, "potential_range_indexes": [ { "index": "a", @@ -10536,7 +11213,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "using_mrr": false, "index_only": true, "rows": 21, - "cost": 5.257301684, + "cost": 0.004153769, "chosen": true } ] @@ -10549,7 +11226,7 @@ explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { "ranges": ["(10) <= (b) <= (50)"] }, "rows_for_plan": 21, - "cost_for_plan": 5.257301684, + "cost_for_plan": 0.004153769, "chosen": true } } @@ -10591,6 +11268,7 @@ analyze table t1; Table Op Msg_type Msg_text test.t1 analyze status Engine-independent statistics collected test.t1 analyze status Table is already up to date +set optimizer_use_condition_selectivity=2; explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; EXPLAIN { @@ -10600,28 +11278,49 @@ EXPLAIN "table_name": "three", "access_type": "ALL", "rows": 3, - "filtered": 100, - "attached_condition": "three.a is not null" + "filtered": 100 }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["a", "b"], + "rows": 10000, + "filtered": 4.311999798, + "attached_condition": "t1.b < 5000 and t1.c < 1000" + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "attached_condition": "t1.a = three.a" + } + } +} +set optimizer_use_condition_selectivity=4; +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +EXPLAIN +{ + "query_block": { + "select_id": 1, "table": { - "table_name": "t1", - "access_type": "ref", - "possible_keys": ["a", "b"], - "key": "a", - "key_length": "5", - "used_key_parts": ["a"], - "ref": ["test.three.a"], - "rowid_filter": { - "range": { - "key": "b", - "used_key_parts": ["b"] - }, - "rows": 4312, - "selectivity_pct": 43.12 + "table_name": "three", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "possible_keys": ["a", "b"], + "rows": 10000, + "filtered": 4.311999798, + "attached_condition": "t1.b < 5000 and t1.c < 1000" }, - "rows": 1000, - "filtered": 4.716249943, - "attached_condition": "t1.b < 5000 and t1.c < 1000" + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "attached_condition": "t1.a = three.a" } } } @@ -10694,8 +11393,8 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "table": "three", "table_scan": { "rows": 3, - "read_cost": 1.002563477, - "read_and_compare_cost": 1.752563477 + "read_cost": 0.010373215, + "read_and_compare_cost": 0.010469215 } }, { @@ -10703,7 +11402,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "range_analysis": { "table_scan": { "rows": 10000, - "cost": 2516.869141 + "cost": 1.5815024 }, "potential_range_indexes": [ { @@ -10727,7 +11426,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "using_mrr": false, "index_only": false, "rows": 4312, - "cost": 3260.045946, + "cost": 5.325058827, "chosen": false, "cause": "cost" } @@ -10748,7 +11447,7 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "rowid_filters": [ { "key": "b", - "build_cost": 174.2257513, + "build_cost": 0.701115436, "rows": 4312 } ] @@ -10784,23 +11483,23 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "access_type": "scan", "rows": 3, - "rows_after_scan": 3, "rows_after_filter": 3, - "cost": 1.752563477, + "rows_out": 3, + "cost": 0.010469215, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 3, - "records_out": 3, - "cost": 1.752563477, + "rows_read": 3, + "rows_out": 3, + "cost": 0.010469215, "uses_join_buffering": false } }, "rows_for_plan": 3, - "cost_for_plan": 1.752563477, + "cost_for_plan": 0.010469215, "rest_of_plan": [ { "plan_prefix": ["three"], @@ -10817,44 +11516,43 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and "cause": "not available", "filter": { "rowid_filter_key": "b", - "index_only_cost": 2.585794484, - "filter_startup_cost": 174.2257513, - "find_key_and_filter_lookup_cost": 96.19157113, + "index_only_cost": 0.092006157, + "filter_startup_cost": 0.701115436, + "find_key_and_filter_lookup_cost": 0.111079997, "filter_selectivity": 0.4312, "orginal_rows": 1000, "new_rows": 431.2, - "original_found_rows_cost": 552.5857945, - "new_found_rows_cost": 335.9373656, - "cost": 1440.757848, + "orginal_access_cost": 1.203290157, + "with_filter_access_cost": 0.682271815, + "original_found_rows_cost": 1.111284, + "with_filter_found_rows_cost": 0.479185661, + "cost": 2.78932608, "filter_used": true }, "rows": 431.2, - "cost": 1440.757848, + "cost": 2.78932608, "chosen": true }, { "access_type": "scan_with_join_cache", "rows": 10000, - "rows_after_scan": 353.71875, - "rows_after_filter": 353.71875, - "cost": 2768.89375, + "rows_after_filter": 471.625, + "rows_out": 431.2, + "cost": 1.71307878, "index_only": false, - "chosen": false + "chosen": true } ], "chosen_access_method": { - "type": "ref", - "records_read": 1000, - "records_out": 353.71875, - "cost": 1440.757848, - "uses_join_buffering": false, - "rowid_filter_key": "b" + "type": "scan", + "rows_read": 471.625, + "rows_out": 431.2, + "cost": 1.71307878, + "uses_join_buffering": true } }, - "rows_for_plan": 3000, - "cost_for_plan": 1442.510412, - "selectivity": 0.0471625, - "estimated_join_cardinality": 141.4875 + "rows_for_plan": 1293.6, + "cost_for_plan": 1.723547995 } ] }, @@ -10869,78 +11567,31 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and { "access_type": "scan", "rows": 10000, - "rows_after_scan": 471.625, "rows_after_filter": 471.625, - "cost": 2516.869141, + "rows_out": 471.625, + "cost": 1.5815024, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", - "records_read": 471.625, - "records_out": 471.625, - "cost": 2516.869141, + "rows_read": 471.625, + "rows_out": 471.625, + "cost": 1.5815024, "uses_join_buffering": false } }, "rows_for_plan": 471.625, - "cost_for_plan": 2516.869141, - "pruned_by_cost": true + "cost_for_plan": 1.5815024, + "pruned_by_heuristic": true } ] }, { "best_join_order": ["three", "t1"], - "rows": 141.4875, - "cost": 1442.510412 - }, - { - "table": "t1", - "range_analysis": { - "table_scan": { - "rows": 10000, - "cost": 1.79769e308 - }, - "potential_range_indexes": [ - { - "index": "a", - "usable": false, - "cause": "not applicable" - }, - { - "index": "b", - "usable": true, - "key_parts": ["b"] - } - ], - "setup_range_conditions": [], - "analyzing_range_alternatives": { - "range_scan_alternatives": [ - { - "index": "b", - "ranges": ["(NULL) < (b) < (5000)"], - "rowid_ordered": false, - "using_mrr": false, - "index_only": true, - "rows": 4312, - "cost": 996.2459458, - "chosen": true - } - ] - }, - "chosen_range_access_summary": { - "range_access_plan": { - "type": "range_scan", - "index": "b", - "rows": 4312, - "ranges": ["(NULL) < (b) < (5000)"] - }, - "rows_for_plan": 4312, - "cost_for_plan": 996.2459458, - "chosen": true - } - } + "rows": 1293.6, + "cost": 1.723547995 }, { "substitute_best_equal": { @@ -10950,15 +11601,64 @@ explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and }, { "attaching_conditions_to_tables": { - "attached_conditions_computation": [], + "attached_conditions_computation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10000, + "cost": 1.5815024 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a"] + }, + { + "index": "b", + "usable": true, + "key_parts": ["b"] + } + ], + "setup_range_conditions": [], + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a" + }, + { + "index": "b", + "ranges": ["(NULL) < (b) < (5000)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 4312, + "cost": 5.325058827, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "not single_table" + } + } + } + ], "attached_conditions_summary": [ { "table": "three", - "attached": "three.a is not null" + "attached": null }, { "table": "t1", - "attached": "t1.b < 5000 and t1.c < 1000" + "attached": "t1.a = three.a and t1.b < 5000 and t1.c < 1000" } ] } |