diff options
| -rw-r--r-- | mysql-test/main/opt_trace.result | 1190 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace.test | 34 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_index_merge.result | 3 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_index_merge_innodb.result | 3 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_security.result | 6 | ||||
| -rw-r--r-- | mysql-test/main/opt_trace_selectivity.result | 6 | ||||
| -rw-r--r-- | sql/opt_trace.cc | 8 | ||||
| -rw-r--r-- | sql/opt_trace.h | 3 | ||||
| -rw-r--r-- | sql/rowid_filter.cc | 22 | ||||
| -rw-r--r-- | sql/rowid_filter.h | 3 | ||||
| -rw-r--r-- | sql/sql_select.cc | 90 | ||||
| -rw-r--r-- | sql/sql_select.h | 19 | ||||
| -rw-r--r-- | sql/table.h | 3 |
13 files changed, 1192 insertions, 198 deletions
diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 1b0dec475eb..3ab761306da 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -148,7 +148,8 @@ select * from v1 { ], "chosen_access_method": { "type": "scan", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 1.502197266, "uses_join_buffering": false } @@ -315,7 +316,8 @@ select * from (select * from t1 where t1.a=1)q { ], "chosen_access_method": { "type": "scan", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 1.502197266, "uses_join_buffering": false } @@ -488,7 +490,8 @@ select * from v2 { ], "chosen_access_method": { "type": "scan", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 1.502197266, "uses_join_buffering": false } @@ -576,7 +579,8 @@ select * from v2 { ], "chosen_access_method": { "type": "scan", - "records": 2, + "records_read": 2, + "records_out": 2, "cost": 2.5, "uses_join_buffering": false } @@ -729,7 +733,8 @@ explain select * from v2 { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.510986328, "uses_join_buffering": false } @@ -862,7 +867,8 @@ explain select * from v1 { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.510986328, "uses_join_buffering": false } @@ -944,7 +950,8 @@ explain select * from v1 { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 12.5, "uses_join_buffering": false } @@ -1133,7 +1140,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ], "chosen_access_method": { "type": "scan", - "records": 100, + "records_read": 100, + "records_out": 100, "cost": 26.15869141, "uses_join_buffering": false } @@ -1158,7 +1166,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ], "chosen_access_method": { "type": "scan", - "records": 100, + "records_read": 100, + "records_out": 100, "cost": 26.15869141, "uses_join_buffering": false } @@ -1203,7 +1212,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ], "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 125.0585794, "uses_join_buffering": false } @@ -1256,7 +1266,8 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ], "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 125.0585794, "uses_join_buffering": false } @@ -1448,7 +1459,8 @@ EXPLAIN SELECT DISTINCT a FROM t1 { ], "chosen_access_method": { "type": "index_merge", - "records": 5, + "records_read": 5, + "records_out": 5, "cost": 6.5, "uses_join_buffering": false } @@ -1654,7 +1666,8 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ], "chosen_access_method": { "type": "scan", - "records": 1, + "records_read": 1, + "records_out": 0.571428573, "cost": 2.084226263, "uses_join_buffering": false } @@ -1887,7 +1900,8 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ], "chosen_access_method": { "type": "index_merge", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25, "uses_join_buffering": false } @@ -2089,7 +2103,8 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ], "chosen_access_method": { "type": "index_merge", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25, "uses_join_buffering": false } @@ -2388,7 +2403,8 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ], "chosen_access_method": { "type": "ref", - "records": 21, + "records_read": 21, + "records_out": 21, "cost": 16.26742739, "uses_join_buffering": false } @@ -2659,7 +2675,8 @@ select t1.a from t1 left join t2 on t1.a=t2.a { ], "chosen_access_method": { "type": "scan", - "records": 4, + "records_read": 4, + "records_out": 4, "cost": 2.003417969, "uses_join_buffering": false } @@ -2816,7 +2833,8 @@ explain select * from t1 left join t2 on t2.a=t1.a { ], "chosen_access_method": { "type": "scan", - "records": 4, + "records_read": 4, + "records_out": 4, "cost": 2.003417969, "uses_join_buffering": false } @@ -2859,7 +2877,8 @@ explain select * from t1 left join t2 on t2.a=t1.a { ], "chosen_access_method": { "type": "eq_ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 5.002147913, "uses_join_buffering": false } @@ -3059,7 +3078,8 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and ], "chosen_access_method": { "type": "scan", - "records": 4, + "records_read": 4, + "records_out": 4, "cost": 2.003417969, "uses_join_buffering": false } @@ -3284,7 +3304,8 @@ explain extended select * from t1 where a in (select pk from t10) { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.510986328, "uses_join_buffering": false } @@ -3327,7 +3348,8 @@ explain extended select * from t1 where a in (select pk from t10) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.753295898, "uses_join_buffering": false } @@ -3352,7 +3374,8 @@ explain extended select * from t1 where a in (select pk from t10) { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.510986328, "uses_join_buffering": false } @@ -3389,7 +3412,8 @@ explain extended select * from t1 where a in (select pk from t10) { ], "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 11.01098633, "uses_join_buffering": true } @@ -3819,7 +3843,8 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ], "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 0.726073957, "uses_join_buffering": false } @@ -3964,7 +3989,8 @@ select f1(a) from t1 { ], "chosen_access_method": { "type": "scan", - "records": 4, + "records_read": 4, + "records_out": 4, "cost": 2.003417969, "uses_join_buffering": false } @@ -4077,7 +4103,8 @@ select f2(a) from t1 { ], "chosen_access_method": { "type": "scan", - "records": 4, + "records_read": 4, + "records_out": 4, "cost": 2.003417969, "uses_join_buffering": false } @@ -4137,7 +4164,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2719 +2766 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -4151,7 +4178,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2619 0 + 2666 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -4159,7 +4186,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2719 0 +select * from t1 2766 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4474,7 +4501,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "range", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.196757383, "uses_join_buffering": false } @@ -4498,7 +4526,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "range", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.196757383, "uses_join_buffering": false } @@ -4539,7 +4568,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 2.176757383, "uses_join_buffering": false } @@ -4589,7 +4619,8 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ], "chosen_access_method": { "type": "ref", - "records": 2, + "records_read": 2, + "records_out": 1.166666667, "cost": 2.853514767, "uses_join_buffering": false } @@ -4752,7 +4783,8 @@ explain select * from (select rand() from t1)q { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -4833,7 +4865,8 @@ explain select * from (select rand() from t1)q { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 3.75, "uses_join_buffering": false } @@ -5058,7 +5091,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5083,7 +5117,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5119,7 +5154,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -5171,7 +5207,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5196,7 +5233,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5221,7 +5259,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5258,7 +5297,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -5283,7 +5323,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -5320,7 +5361,8 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 9.852563477, "uses_join_buffering": true } @@ -5705,7 +5747,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5730,7 +5773,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5755,7 +5799,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -5780,7 +5825,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -5805,7 +5851,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -5830,7 +5877,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -5867,7 +5915,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -5892,7 +5941,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -5917,7 +5967,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -5942,7 +5993,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -5967,7 +6019,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -6004,7 +6057,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 76.15769043, "uses_join_buffering": true } @@ -6029,7 +6083,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 26.05256348, "uses_join_buffering": true } @@ -6054,7 +6109,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 26.05256348, "uses_join_buffering": true } @@ -6079,7 +6135,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 76.15769043, "uses_join_buffering": true } @@ -6116,7 +6173,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -6141,7 +6199,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 86.80256348, "uses_join_buffering": true } @@ -6166,7 +6225,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -6225,7 +6285,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 34.15256348, "uses_join_buffering": true } @@ -6250,7 +6311,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 100.4576904, "uses_join_buffering": true } @@ -6298,7 +6360,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 331.3076904, "uses_join_buffering": true } @@ -6387,7 +6450,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -6412,7 +6476,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -6517,7 +6582,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -6542,7 +6608,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -6567,7 +6634,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 9.852563477, "uses_join_buffering": true } @@ -6592,7 +6660,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -6629,7 +6698,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -6654,7 +6724,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 86.80256348, "uses_join_buffering": true } @@ -6679,7 +6750,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -6733,7 +6805,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 34.15256348, "uses_join_buffering": true } @@ -6758,7 +6831,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 100.4576904, "uses_join_buffering": true } @@ -6806,7 +6880,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 331.3076904, "uses_join_buffering": true } @@ -6898,7 +6973,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -6923,7 +6999,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -7019,7 +7096,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 12.70769043, "uses_join_buffering": true } @@ -7044,7 +7122,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.902563477, "uses_join_buffering": true } @@ -7069,7 +7148,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 12.70769043, "uses_join_buffering": true } @@ -7111,7 +7191,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 34.15256348, "uses_join_buffering": true } @@ -7136,7 +7217,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 100.4576904, "uses_join_buffering": true } @@ -7184,7 +7266,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 331.3076904, "uses_join_buffering": true } @@ -7273,7 +7356,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 35.65769043, "uses_join_buffering": true } @@ -7298,7 +7382,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 35.65769043, "uses_join_buffering": true } @@ -7346,7 +7431,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 331.3076904, "uses_join_buffering": true } @@ -7438,7 +7524,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -7463,7 +7550,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -7488,7 +7576,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -7530,7 +7619,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -7555,7 +7645,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 878.0576904, "uses_join_buffering": true } @@ -7716,7 +7807,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 47.31921387, "uses_join_buffering": false } @@ -7742,7 +7834,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 263.8729248, "uses_join_buffering": false } @@ -7773,7 +7866,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 5.25769043, "uses_join_buffering": false } @@ -7799,7 +7893,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 29.31921387, "uses_join_buffering": false } @@ -8122,7 +8217,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -8147,7 +8243,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -8183,7 +8280,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -8232,7 +8330,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -8257,7 +8356,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -8293,7 +8393,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -8345,7 +8446,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -8370,7 +8472,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -8395,7 +8498,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -8420,7 +8524,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -8445,7 +8550,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -8470,7 +8576,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 3.25769043, "uses_join_buffering": false } @@ -8507,7 +8614,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -8532,7 +8640,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -8557,7 +8666,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -8582,7 +8692,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.002563477, "uses_join_buffering": true } @@ -8607,7 +8718,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 10.00769043, "uses_join_buffering": true } @@ -8644,7 +8756,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 76.15769043, "uses_join_buffering": true } @@ -8669,7 +8782,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 26.05256348, "uses_join_buffering": true } @@ -8694,7 +8808,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 26.05256348, "uses_join_buffering": true } @@ -8719,7 +8834,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 76.15769043, "uses_join_buffering": true } @@ -8756,7 +8872,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -8781,7 +8898,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 86.80256348, "uses_join_buffering": true } @@ -8806,7 +8924,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -8870,7 +8989,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 34.15256348, "uses_join_buffering": true } @@ -8895,7 +9015,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 100.4576904, "uses_join_buffering": true } @@ -8943,7 +9064,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 331.3076904, "uses_join_buffering": true } @@ -9086,7 +9208,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -9111,7 +9234,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -9136,7 +9260,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 9.852563477, "uses_join_buffering": true } @@ -9161,7 +9286,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 27.55769043, "uses_join_buffering": true } @@ -9198,7 +9324,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -9223,7 +9350,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 86.80256348, "uses_join_buffering": true } @@ -9248,7 +9376,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 258.4076904, "uses_join_buffering": true } @@ -9351,7 +9480,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 12.70769043, "uses_join_buffering": true } @@ -9376,7 +9506,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 4.902563477, "uses_join_buffering": true } @@ -9401,7 +9532,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 12.70769043, "uses_join_buffering": true } @@ -9443,7 +9575,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 34.15256348, "uses_join_buffering": true } @@ -9468,7 +9601,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 100.4576904, "uses_join_buffering": true } @@ -9542,7 +9676,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 35.65769043, "uses_join_buffering": true } @@ -9567,7 +9702,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 35.65769043, "uses_join_buffering": true } @@ -9646,7 +9782,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -9671,7 +9808,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -9696,7 +9834,8 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ], "chosen_access_method": { "type": "scan", - "records": 9, + "records_read": 9, + "records_out": 9, "cost": 88.30769043, "uses_join_buffering": true } @@ -10449,7 +10588,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 5, + "records_read": 5, + "records_out": 5, "cost": 3.508544922, "uses_join_buffering": false } @@ -10478,7 +10618,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 800, + "records_read": 800, + "records_out": 800, "cost": 252.0986328, "uses_join_buffering": false } @@ -10522,7 +10663,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 800, + "records_read": 800, + "records_out": 800, "cost": 1260.493164, "uses_join_buffering": false } @@ -10587,7 +10729,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.508544922, "uses_join_buffering": false } @@ -10616,7 +10759,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 800, + "records_read": 800, + "records_out": 800, "cost": 252.0986328, "uses_join_buffering": false } @@ -10669,7 +10813,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 12.50585794, "uses_join_buffering": false } @@ -10835,7 +10980,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 10, + "records_read": 10, + "records_out": 10, "cost": 3.510986328, "uses_join_buffering": false } @@ -10865,7 +11011,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "scan", - "records": 100, + "records_read": 100, + "records_out": 100, "cost": 26.10986328, "uses_join_buffering": false } @@ -10918,7 +11065,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 12.50585794, "uses_join_buffering": false } @@ -11174,7 +11322,8 @@ select count(*) from seq_1_to_10000000 { ], "chosen_access_method": { "type": "scan", - "records": 10000000, + "records_read": 10000000, + "records_out": 10000000, "cost": 7250000, "uses_join_buffering": false } @@ -11565,7 +11714,8 @@ json_detailed(json_extract(trace, '$**.choose_best_splitting')) "chosen_access_method": { "type": "ref", - "records": 1.8367, + "records_read": 1.8367, + "records_out": 1.8367, "cost": 1.417925794, "uses_join_buffering": false } @@ -11653,3 +11803,737 @@ left(trace, 100) set optimizer_trace='enabled=off'; # End of 10.6 tests +# +# Testing of records_out +# +set @save_optimizer_switch= @@optimizer_switch; +set @save_use_stat_tables= @@use_stat_tables; +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; +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 +explain select * from t1 where a<10 and b between 10 and 50 and c < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range|filter a,b a|b 5|5 NULL 9 (2%) Using index condition; Using where; Using rowid filter +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain select * from t1 where a<10 and b between 10 and 50 and c < 10 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c from t1 where t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 252.5869141 + }, + "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", + "ranges": ["(NULL) < (a) < (10)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 9, + "cost": 7.27527215, + "chosen": true + }, + { + "index": "b", + "ranges": ["(10) <= (b) <= (50)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 21, + "cost": 16.28230168, + "chosen": false, + "cause": "cost" + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 9, + "ranges": ["(NULL) < (a) < (10)"] + }, + "rows_for_plan": 9, + "cost_for_plan": 7.27527215, + "chosen": true + } + } + }, + { + "table": "t1", + "rowid_filters": [ + { + "key": "a", + "build_cost": 0.759047172, + "rows": 9 + }, + { + "key": "b", + "build_cost": 1.122236655, + "rows": 21 + } + ] + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.009 + }, + { + "index_name": "b", + "selectivity_from_index": 0.021 + } + ], + "selectivity_for_columns": [ + { + "column_name": "c", + "ranges": ["NULL < c < 10"], + "selectivity_from_histogram": 0.094 + } + ], + "cond_selectivity": 0.000017766 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "filter": { + "rowid_filter_key": "b", + "index_only_cost": 0.50527215, + "filter_startup_cost": 1.122236655, + "find_key_and_filter_lookup_cost": 0.386507019, + "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, + "filter_used": true + }, + "access_type": "range", + "rows": 9, + "rows_after_scan": 9, + "rows_after_filter": 0.189, + "cost": 2.54120511, + "chosen": true + } + ], + "chosen_access_method": { + "type": "range", + "records_read": 9, + "records_out": 0.017766, + "cost": 2.54120511, + "uses_join_buffering": false, + "rowid_filter_key": "b" + } + } + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "rows_for_plan": 9, + "cost_for_plan": 2.54120511 + } + ] + }, + { + "best_join_order": ["t1"], + "rows": 9, + "cost": 2.54120511 + }, + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "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": ["(10) <= (b) <= (50)"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": true, + "rows": 21, + "cost": 5.257301684, + "chosen": true + } + ] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "b", + "rows": 21, + "ranges": ["(10) <= (b) <= (50)"] + }, + "rows_for_plan": 21, + "cost_for_plan": 5.257301684, + "chosen": true + } + } + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a < 10 and t1.b between 10 and 50 and t1.c < 10" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table t1; +create table three (a int); +insert into three values (1),(2),(3); +create table t1 (a int, b int, c int, key(a),key(b)); +insert into t1 select mod(seq,10), seq, seq from seq_1_to_10000; +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 +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, + "nested_loop": [ + { + "table": { + "table_name": "three", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "three.a is not null" + } + }, + { + "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 + }, + "rows": 1000, + "filtered": 4.307688236, + "attached_condition": "t1.b < 5000 and t1.c < 1000" + } + } + ] + } +} +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select three.a AS a,t1.a AS a,t1.b AS b,t1.c AS c from three join t1 where t1.a = three.a and t1.b < 5000 and t1.c < 1000" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = three.a and t1.b < 5000 and t1.c < 1000", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.b < 5000 and t1.c < 1000 and multiple equal(t1.a, three.a)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "three", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + }, + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 1, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "index": "a", + "field": "a", + "equals": "three.a", + "null_rejecting": true + } + ] + }, + { + "rows_estimation": [ + { + "table": "three", + "table_scan": { + "rows": 3, + "read_cost": 1.002563477, + "read_and_compare_cost": 1.752563477 + } + }, + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 10000, + "cost": 2516.869141 + }, + "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": false, + "rows": 4312, + "cost": 3260.045946, + "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" + } + } + }, + { + "table": "t1", + "rowid_filters": [ + { + "key": "b", + "build_cost": 174.2257513, + "rows": 4312 + } + ] + }, + { + "selectivity_for_indexes": [ + { + "index_name": "b", + "selectivity_from_index": 0.4312 + } + ], + "selectivity_for_columns": [ + { + "column_name": "c", + "ranges": ["NULL < c < 1000"], + "selectivity_from_histogram": 0.0999 + } + ], + "cond_selectivity": 0.04307688 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "three", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 3, + "rows_after_scan": 3, + "rows_after_filter": 3, + "cost": 1.752563477, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records_read": 3, + "records_out": 3, + "cost": 1.752563477, + "uses_join_buffering": false + } + } + }, + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 1 + }, + "considered_access_paths": [ + { + "access_type": "scan", + "rows": 10000, + "rows_after_scan": 430.7688, + "rows_after_filter": 430.7688, + "cost": 2516.869141, + "index_only": false, + "chosen": true + } + ], + "chosen_access_method": { + "type": "scan", + "records_read": 430.7688, + "records_out": 430.7688, + "cost": 2516.869141, + "uses_join_buffering": false + } + } + } + ] + }, + { + "plan_prefix": [], + "table": "three", + "rows_for_plan": 3, + "cost_for_plan": 1.752563477, + "rest_of_plan": [ + { + "plan_prefix": ["three"], + "get_costs_for_tables": [ + { + "best_access_path": { + "table": "t1", + "plan_details": { + "record_count": 3 + }, + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": false, + "reason": "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, + "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, + "filter_used": true + }, + "rows": 431.2, + "cost": 1440.757848, + "chosen": true + }, + { + "access_type": "scan_with_join_cache", + "rows": 10000, + "rows_after_scan": 323.0766, + "rows_after_filter": 323.0766, + "cost": 2759.176591, + "index_only": false, + "chosen": false + } + ], + "chosen_access_method": { + "type": "ref", + "records_read": 1000, + "records_out": 323.0766, + "cost": 1440.757848, + "uses_join_buffering": false, + "rowid_filter_key": "b" + } + } + } + ] + }, + { + "plan_prefix": ["three"], + "table": "t1", + "rows_for_plan": 3000, + "cost_for_plan": 1442.510412, + "selectivity": 0.04307688, + "estimated_join_cardinality": 129.23064 + } + ] + }, + { + "plan_prefix": [], + "table": "t1", + "rows_for_plan": 430.7688, + "cost_for_plan": 2516.869141, + "pruned_by_cost": true, + "current_cost": 2516.869141, + "best_cost": 1442.510512 + } + ] + }, + { + "best_join_order": ["three", "t1"], + "rows": 129.23064, + "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 + } + } + }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = three.a and t1.b < 5000 and t1.c < 1000" + } + }, + { + "attaching_conditions_to_tables": { + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "three", + "attached": "three.a is not null" + }, + { + "table": "t1", + "attached": "t1.b < 5000 and t1.c < 1000" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table three, t1; +set @@optimizer_switch= @save_optimizer_switch; +set @@use_stat_tables= @save_use_stat_tables; +set @@histogram_size= @save_histogram_size; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index d07afb2dfce..cf54c8771d0 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -928,3 +928,37 @@ set optimizer_trace='enabled=off'; --echo # End of 10.6 tests + +--echo # +--echo # Testing of records_out +--echo # + +set @save_optimizer_switch= @@optimizer_switch; +set @save_use_stat_tables= @@use_stat_tables; +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; +analyze table t1; +--optimizer_trace +explain select * from t1 where a<10 and b between 10 and 50 and c < 10; +drop table t1; + +create table three (a int); +insert into three values (1),(2),(3); +create table t1 (a int, b int, c int, key(a),key(b)); +insert into t1 select mod(seq,10), seq, seq from seq_1_to_10000; +analyze table t1; + +--optimizer_trace +explain format=json select * from three, t1 where t1.a=three.a and t1.b<5000 and t1.c<1000; +drop table three, t1; + +set @@optimizer_switch= @save_optimizer_switch; +set @@use_stat_tables= @save_use_stat_tables; +set @@histogram_size= @save_histogram_size; +set @@optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index c4ca31fcad9..7bf2a89705c 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -226,7 +226,8 @@ explain select * from t1 where a=1 or b=1 { ], "chosen_access_method": { "type": "index_merge", - "records": 2, + "records_read": 2, + "records_out": 2, "cost": 2.601171589, "uses_join_buffering": false } diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index b7390cff757..d0a4f7cd24b 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -226,7 +226,8 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { ], "chosen_access_method": { "type": "ref", - "records": 1, + "records_read": 1, + "records_out": 1, "cost": 1.250146475, "uses_join_buffering": false } diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index 414ff2e4a0b..5334ed8b2c4 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -110,7 +110,8 @@ select * from db1.t1 { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } @@ -248,7 +249,8 @@ select * from db1.v1 { ], "chosen_access_method": { "type": "scan", - "records": 3, + "records_read": 3, + "records_out": 3, "cost": 1.752563477, "uses_join_buffering": false } diff --git a/mysql-test/main/opt_trace_selectivity.result b/mysql-test/main/opt_trace_selectivity.result index bed10cd56e1..bef3d62b0ce 100644 --- a/mysql-test/main/opt_trace_selectivity.result +++ b/mysql-test/main/opt_trace_selectivity.result @@ -86,7 +86,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "index_merge", - "records": 7, + "records_read": 7, + "records_out": 7, "cost": 13.79559815, "uses_join_buffering": false } @@ -177,7 +178,8 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "chosen_access_method": { "type": "ref", - "records": 6, + "records_read": 6, + "records_out": 0.6, "cost": 5.002343464, "uses_join_buffering": false } diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index 744959eb164..374fc41aba8 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -689,15 +689,15 @@ void print_final_join_order(JOIN *join) } -void print_best_access_for_table(THD *thd, POSITION *pos, - enum join_type type) +void print_best_access_for_table(THD *thd, POSITION *pos) { DBUG_ASSERT(thd->trace_started()); Json_writer_object obj(thd, "chosen_access_method"); obj. - add("type", type == JT_ALL ? "scan" : join_type_str[type]). - add("records", pos->records_read). + add("type", pos->type == JT_ALL ? "scan" : join_type_str[pos->type]). + add("records_read", pos->records_read). + add("records_out", pos->records_out). add("cost", pos->read_time). add("uses_join_buffering", pos->use_join_buffer); if (pos->range_rowid_filter_info) diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 1ee23a33591..a43c1dde54b 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -109,8 +109,7 @@ void opt_trace_print_expanded_query(THD *thd, SELECT_LEX *select_lex, void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab); void trace_plan_prefix(JOIN *join, uint idx, table_map join_tables); void print_final_join_order(JOIN *join); -void print_best_access_for_table(THD *thd, POSITION *pos, - enum join_type type); +void print_best_access_for_table(THD *thd, POSITION *pos); void trace_condition(THD * thd, const char *name, const char *transform_type, Item *item, const char *table_name= nullptr); diff --git a/sql/rowid_filter.cc b/sql/rowid_filter.cc index 1926b43188a..c0f7fe0755a 100644 --- a/sql/rowid_filter.cc +++ b/sql/rowid_filter.cc @@ -460,10 +460,16 @@ void Range_rowid_filter_cost_info::trace_info(THD *thd) @brief Choose the best range filter for the given access of the table - @param access_key_no The index by which the table is accessed - @param records The estimated total number of key tuples with this access - @param access_cost_factor the cost of a random seek to access the table - + @param access_key_no The index by which the table is accessed + @param records The estimated total number of key tuples with + this access + @param fetch_cost_factor The cost of fetching 'records' rows + @param index_only_cost The cost of fetching 'records' rows with + index only reads + @param prev_records How many row combinations we have in + preceding tables + @parma records_out Will be updated to the minimum result rows for any + usable filter. @details The function looks through the array of cost info for range filters and chooses the element for the range filter that promise the greatest @@ -478,7 +484,8 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no, double records, double fetch_cost, double index_only_cost, - double prev_records) + double prev_records, + double *records_out) { if (range_rowid_filter_cost_info_elems == 0 || covering_keys.is_set(access_key_no)) @@ -521,13 +528,14 @@ TABLE::best_range_rowid_filter_for_partial_join(uint access_key_no, continue; new_records= records * filter->selectivity; + set_if_smaller(*records_out, new_records); cost_of_accepted_rows= fetch_cost * filter->selectivity; cost_of_rejected_rows= index_only_cost * (1 - filter->selectivity); new_cost= (cost_of_accepted_rows + cost_of_rejected_rows + records * filter->lookup_cost()); new_total_cost= ((new_cost + new_records * - in_use->variables.optimizer_where_cost) * prev_records + - filter->get_setup_cost()); + in_use->variables.optimizer_where_cost) * + prev_records + filter->get_setup_cost()); if (best_filter_gain > new_total_cost) { diff --git a/sql/rowid_filter.h b/sql/rowid_filter.h index dc64dc876ce..f7386631c3d 100644 --- a/sql/rowid_filter.h +++ b/sql/rowid_filter.h @@ -491,7 +491,8 @@ public: double records, double fetch_cost, double index_only_cost, - double prev_records); + double prev_records, + double *records_out); Range_rowid_filter_cost_info * apply_filter(THD *thd, TABLE *table, double *cost, double *records_arg, double *startup_cost, double fetch_cost, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7060da031a7..11c3299a8d8 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -89,6 +89,18 @@ */ #define HASH_FANOUT 0.1 +/* + The following is used to check that A <= B, but with some margin as the + calculation is done slightly differently (mathematically correct, but + double calculations are not exact). + This is only used when comparing read rows and output rows, which + means that we can assume that both values are >= 0 and B cannot be notable + smaller than A. +*/ + +#define crash_if_first_double_is_bigger(A,B) DBUG_ASSERT(((A) == 0.0 && (B) == 0.0) || (A)/(B) < 1.0000001) + + /* Cost for reading a row through an index */ struct INDEX_READ_COST { @@ -318,7 +330,7 @@ static JOIN_TAB *next_breadth_first_tab(JOIN_TAB *first_top_tab, static bool find_order_in_list(THD *, Ref_ptr_array, TABLE_LIST *, ORDER *, List<Item> &, List<Item> &, bool, bool, bool); -static double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, +static double table_after_join_selectivity(JOIN *join, uint idx, JOIN_TAB *s, table_map rem_tables); void set_postjoin_aggr_write_func(JOIN_TAB *tab); @@ -421,7 +433,7 @@ bool dbug_user_var_equals_str(THD *thd, const char *name, const char* value) POSITION::POSITION() { table= 0; - records_read= cond_selectivity= read_time= 0.0; + records_read= cond_selectivity= read_time= records_out= 0.0; prefix_record_count= 0.0; key= 0; use_join_buffer= 0; @@ -5818,6 +5830,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, Json_writer_object table_records(thd); /* Only one matching row */ s->found_records= s->records= 1; + s->records_out= 1.0; s->read_time=1.0; s->worst_seeks=1.0; table_records.add_table_name(s) @@ -7686,6 +7699,7 @@ void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) join->positions[idx].table= table; join->positions[idx].key=key; join->positions[idx].records_read=1.0; /* This is a const table */ + join->positions[idx].records_out=1.0; /* This is a const table */ join->positions[idx].cond_selectivity= 1.0; join->positions[idx].ref_depend_map= 0; @@ -8022,6 +8036,7 @@ best_access_path(JOIN *join, my_bool found_constraint= 0; double best_cost= DBL_MAX; double records= DBL_MAX; + double records_out= table->stat_records() * table->cond_selectivity; table_map best_ref_depends_map= 0; /* key_dependent is 0 if all key parts could be used or if there was an @@ -8317,9 +8332,12 @@ best_access_path(JOIN *join, (1.0 + ((double) (table->s->max_key_length-keyinfo->key_length) / (double) table->s->max_key_length))); + set_if_smaller(records, (double)s->records); + set_if_smaller(records_out, records); if (records < 2.0) records=2.0; /* Can't be as good as a unique */ } + /* ReuseRangeEstimateForRef-2: We get here if we could not reuse E(#rows) from range optimizer. Make another try: @@ -8357,9 +8375,10 @@ best_access_path(JOIN *join, } } /* Calculate the cost of the index access */ - INDEX_READ_COST cost= cost_for_index_read(thd, table, key, - (ha_rows) records, - (ha_rows) s->worst_seeks); + INDEX_READ_COST cost= + cost_for_index_read(thd, table, key, + (ha_rows) records, + (ha_rows) s->worst_seeks); tmp= cost.read_cost; index_only_cost= cost.index_only_cost; } @@ -8631,7 +8650,8 @@ best_access_path(JOIN *join, records, tmp, index_only_cost, - record_count); + record_count, + &records_out); if (filter) filter= filter->apply_filter(thd, table, &tmp, &records_after_filter, &startup_cost, @@ -8673,6 +8693,7 @@ best_access_path(JOIN *join, add("chosen", false). add("cause", cause ? cause : "cost"); } + set_if_smaller(records_out, records); } /* for each key */ records= best_records; } @@ -8726,6 +8747,8 @@ best_access_path(JOIN *join, /* Estimate the cost of the hash join access to the table */ double rnd_records= matching_candidates_in_table(s, found_constraint, use_cond_selectivity); + set_if_smaller(records_out, rnd_records); + /* The following cost calculation is identical to the cost calculation for the join cache later on, except for the HASH_FANOUT @@ -8876,7 +8899,8 @@ best_access_path(JOIN *join, table->best_range_rowid_filter_for_partial_join(key_no, rows2double(range->rows), range->find_cost, range->index_only_cost, - record_count); + record_count, + &records_out); if (filter) { double filter_cost= range->fetch_cost; @@ -8905,6 +8929,7 @@ best_access_path(JOIN *join, { type= JT_INDEX_MERGE; } + set_if_smaller(records_out, records_after_filter); loose_scan_opt.check_range_access(join, idx, s->quick); } else @@ -8913,7 +8938,10 @@ best_access_path(JOIN *join, rnd_records= matching_candidates_in_table(s, found_constraint, use_cond_selectivity); records_after_filter= rnd_records; + set_if_smaller(records_out, rnd_records); + org_records= rows2double(s->records); + DBUG_ASSERT(rnd_records <= s->records); /* Estimate cost of reading table. */ @@ -9043,7 +9071,9 @@ best_access_path(JOIN *join, } /* Update the cost information for the current partial plan */ + crash_if_first_double_is_bigger(records_out, records); pos->records_read= records; + pos->records_out= records_out; pos->read_time= best_cost; pos->key= best_key; pos->type= best_type; @@ -9070,7 +9100,7 @@ best_access_path(JOIN *join, trace_paths.end(); if (unlikely(thd->trace_started())) - print_best_access_for_table(thd, pos, best_type); + print_best_access_for_table(thd, pos); DBUG_VOID_RETURN; } @@ -9575,8 +9605,8 @@ optimize_straight_join(JOIN *join, table_map remaining_tables) remaining_tables&= ~(s->table->map); double pushdown_cond_selectivity= 1.0; if (use_cond_selectivity > 1) - pushdown_cond_selectivity= table_cond_selectivity(join, idx, s, - remaining_tables); + pushdown_cond_selectivity= table_after_join_selectivity(join, idx, s, + remaining_tables); position->cond_selectivity= pushdown_cond_selectivity; ++idx; } @@ -10113,8 +10143,8 @@ double table_multi_eq_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, */ static -double table_cond_selectivity(JOIN *join, uint idx, JOIN_TAB *s, - table_map rem_tables) +double table_after_join_selectivity(JOIN *join, uint idx, JOIN_TAB *s, + table_map rem_tables) { uint16 ref_keyuse_steps_buf[MAX_REF_PARTS]; uint ref_keyuse_size= MAX_REF_PARTS; @@ -10890,9 +10920,10 @@ best_extension_by_limited_search(JOIN *join, pushdown_cond_selectivity= 1.0; if (use_cond_selectivity > 1) - pushdown_cond_selectivity= table_cond_selectivity(join, idx, s, - remaining_tables & - ~real_table_bit); + pushdown_cond_selectivity= + table_after_join_selectivity(join, idx, s, + remaining_tables & ~real_table_bit); + join->positions[idx].cond_selectivity= pushdown_cond_selectivity; partial_join_cardinality= (current_record_count * @@ -10903,8 +10934,9 @@ best_extension_by_limited_search(JOIN *join, .add("selectivity", pushdown_cond_selectivity) .add("estimated_join_cardinality", partial_join_cardinality); - if ((search_depth > 1) && + if (search_depth > 1 && ((remaining_tables & ~real_table_bit) & allowed_tables)) + { /* Recursively expand the current partial plan */ Json_writer_array trace_rest(thd, "rest_of_plan"); @@ -11715,6 +11747,7 @@ bool JOIN::get_best_combination() */ SJ_MATERIALIZATION_INFO *sjm= cur_pos->table->emb_sj_nest->sj_mat_info; j->records_read= (sjm->is_sj_scan? sjm->rows : 1.0); + j->records_out= j->records_read; j->records= (ha_rows) j->records_read; j->cond_selectivity= 1.0; JOIN_TAB *jt; @@ -11770,8 +11803,15 @@ bool JOIN::get_best_combination() to access join->best_positions[]. */ j->records_read= best_positions[tablenr].records_read; + j->records_out= best_positions[tablenr].records_out; j->cond_selectivity= best_positions[tablenr].cond_selectivity; DBUG_ASSERT(j->cond_selectivity <= 1.0); + crash_if_first_double_is_bigger(j->records_out, + j->records_read * + (j->range_rowid_filter_info ? + j->range_rowid_filter_info->selectivity : + 1.0)); + map2table[j->table->tablenr]= j; /* If we've reached the end of sjm nest, switch back to main sequence */ @@ -13071,7 +13111,12 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) /* Fix for EXPLAIN */ if (sel->quick) - join->best_positions[i].records_read= (double)sel->quick->records; + { + join->best_positions[i].records_read= + (double) sel->quick->records; + set_if_smaller(join->best_positions[i].records_out, + join->best_positions[i].records_read); + } } else { @@ -18542,9 +18587,10 @@ void optimize_wo_join_buffering(JOIN *join, uint first_tab, uint last_tab, table_map real_table_bit= rs->table->map; if (join->thd->variables.optimizer_use_condition_selectivity > 1) { - pushdown_cond_selectivity= table_cond_selectivity(join, i, rs, - reopt_remaining_tables & - ~real_table_bit); + pushdown_cond_selectivity= + table_after_join_selectivity(join, i, rs, + reopt_remaining_tables & + ~real_table_bit); } (*outer_rec_count) *= pushdown_cond_selectivity; if (!rs->emb_sj_nest) @@ -22673,7 +22719,7 @@ join_read_const_table(THD *thd, JOIN_TAB *tab, POSITION *pos) { // Info for DESCRIBE tab->info= ET_CONST_ROW_NOT_FOUND; /* Mark for EXPLAIN that the row was not found */ - pos->records_read=0.0; + pos->records_read= pos->records_out= 0.0; pos->ref_depend_map= 0; if (!table->pos_in_table_list->outer_join || error > 0) DBUG_RETURN(error); @@ -22691,7 +22737,7 @@ join_read_const_table(THD *thd, JOIN_TAB *tab, POSITION *pos) { tab->info= ET_UNIQUE_ROW_NOT_FOUND; /* Mark for EXPLAIN that the row was not found */ - pos->records_read=0.0; + pos->records_read= pos->records_out= 0.0; pos->ref_depend_map= 0; if (!table->pos_in_table_list->outer_join || error > 0) DBUG_RETURN(error); diff --git a/sql/sql_select.h b/sql/sql_select.h index 106746b35de..ec13eecd4d6 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -343,7 +343,10 @@ typedef struct st_join_table { /* Copy of POSITION::records_read, set by get_best_combination() */ double records_read; - + + /* Copy of POSITION::records_out, set by get_best_combination() */ + double records_out; + /* The selectivity of the conditions that can be pushed to the table */ double cond_selectivity; @@ -939,11 +942,22 @@ public: JOIN_TAB *table; /* + The number of rows that will be read from the table + */ + double records_read; + + /* The "fanout": number of output rows that will be produced (after pushed down selection condition is applied) per each row combination of previous tables. + + This takes into account table->cond_selectivity, the WHERE clause + related to this table calculated in + calculate_cond_selectivity_for_table(), and the used rowid filter but + does not take into account the WHERE clause involving preceding tables + calculated in table_after_join_selectivity(). */ - double records_read; + double records_out; /* The selectivity of the pushed down conditions */ double cond_selectivity; @@ -1007,6 +1021,7 @@ public: /* Type of join (EQ_REF, REF etc) */ enum join_type type; + /* Valid only after fix_semijoin_strategies_for_picked_join_order() call: if sj_strategy!=SJ_OPT_NONE, this is the number of subsequent tables that diff --git a/sql/table.h b/sql/table.h index bd1816175df..4350c33d389 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1810,7 +1810,8 @@ public: double records, double fetch_cost, double index_only_cost, - double prev_records); + double prev_records, + double *records_out); /** System Versioning support */ |