revision-id: 595b916272e377bb00298e603abd9541dede9bf4 (mariadb-10.4.27-71-g595b916) parent(s): c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7 author: Igor Babaev committer: Igor Babaev timestamp: 2023-01-31 00:10:41 -0800 message: MDEV-30218 Incorrect optimization for rowid_filtering Correction over ths last patch for this MDEV. --- mysql-test/main/join_nested_jcl6.result | 2 +- mysql-test/main/opt_trace.result | 13 +- .../main/opt_trace_index_merge_innodb.result | 1 - mysql-test/main/range.result | 2 +- mysql-test/main/rowid_filter.result | 189 ++++++++++++--------- mysql-test/main/rowid_filter_innodb.result | 102 ++++++++--- mysql-test/main/select.result | 2 +- mysql-test/main/select_jcl6.result | 2 +- mysql-test/main/select_pkeycache.result | 2 +- mysql-test/main/selectivity.result | 2 +- sql/sql_select.cc | 132 +++++++------- 11 files changed, 251 insertions(+), 198 deletions(-) diff --git a/mysql-test/main/join_nested_jcl6.result b/mysql-test/main/join_nested_jcl6.result index 7226a5d..91fe367 100644 --- a/mysql-test/main/join_nested_jcl6.result +++ b/mysql-test/main/join_nested_jcl6.result @@ -2085,7 +2085,7 @@ ON t6.b >= 2 AND t5.b=t7.b AND (t8.a > 0 OR t8.c IS NULL) AND t6.a>0 AND t7.a>0; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t5 ALL NULL NULL NULL NULL 3 -1 SIMPLE t7 ref PRIMARY,b_i b_i 5 test.t5.b 2 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE t7 ref|filter PRIMARY,b_i b_i|PRIMARY 5|4 test.t5.b 2 (29%) Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter 1 SIMPLE t6 range PRIMARY,b_i PRIMARY 4 NULL 3 Using where; Rowid-ordered scan; Using join buffer (incremental, BNL join) 1 SIMPLE t8 ref b_i b_i 5 test.t5.b 2 Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 2eef0da..92cd3eb 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -1016,7 +1016,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "index": "a", "used_range_estimates": false, "cause": "not available", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 200, "chosen": true @@ -1073,7 +1072,6 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { "index": "a", "used_range_estimates": false, "cause": "not available", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 200, "chosen": true @@ -2120,7 +2118,6 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { "access_type": "ref", "index": "a_c", "used_range_estimates": true, - "rowid_filter_skipped": "worst/max seeks clipping", "rows": 180, "cost": 92, "chosen": true @@ -3346,7 +3343,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "access_type": "ref", "index": "pk", "used_range_estimates": true, - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 2, "chosen": true @@ -3355,7 +3351,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "access_type": "ref", "index": "pk_a", "used_range_estimates": true, - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 2, "chosen": false, @@ -3365,7 +3360,6 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { "access_type": "ref", "index": "pk_a_b", "used_range_estimates": true, - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 1.0043, "chosen": true @@ -3974,6 +3968,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "best_access_path": { "considered_access_paths": [ { + "rowid_filter_skipped": "cost_factor <= 0", "access_type": "range", "resulting_rows": 3, "cost": 1.407, @@ -4000,7 +3995,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "index": "a", "used_range_estimates": false, "cause": "not better than ref estimates", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 3.007, "chosen": true @@ -4030,6 +4024,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "best_access_path": { "considered_access_paths": [ { + "rowid_filter_skipped": "cost_factor <= 0", "access_type": "range", "resulting_rows": 3, "cost": 1.407, @@ -4056,7 +4051,6 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "index": "a", "used_range_estimates": false, "cause": "not better than ref estimates", - "rowid_filter_skipped": "worst/max seeks clipping", "rows": 2, "cost": 3.014, "chosen": true @@ -8069,7 +8063,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "index": "b", "used_range_estimates": false, "cause": "not available", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 20, "chosen": true @@ -8273,7 +8266,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "index": "a", "used_range_estimates": false, "cause": "not available", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 20, "chosen": true @@ -8341,7 +8333,6 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.considered_execution_plans')) "index": "a", "used_range_estimates": false, "cause": "not available", - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 200, "chosen": true diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 82f09df..5786f74 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -208,7 +208,6 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { "access_type": "ref", "index": "key1", "used_range_estimates": true, - "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 2, "chosen": true diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index a3ce10f..0e728d7 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -281,7 +281,7 @@ INSERT INTO t1 VALUES (33,5),(33,5),(33,5),(33,5),(34,5),(35,5); EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range a,b a 5 NULL 2 Using index condition; Using where +1 SIMPLE t1 ref|filter a,b b|a 5|5 const 15 (5%) Using where; Using rowid filter SELECT * FROM t1 WHERE a IN(1,2) AND b=5; a b DROP TABLE t1; diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index b35021b..efe1fe1 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -335,8 +335,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 Using index condition -1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) Using where; Using rowid filter +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 Using index condition +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for EXPLAIN FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -346,40 +346,40 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "lineitem", + "table_name": "orders", "access_type": "range", + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], + "rows": 69, + "filtered": 100, + "index_condition": "orders.o_totalprice between 200000 and 230000" + }, + "table": { + "table_name": "lineitem", + "access_type": "ref", "possible_keys": [ "PRIMARY", "i_l_shipdate", "i_l_orderkey", "i_l_orderkey_quantity" ], - "key": "i_l_shipdate", - "key_length": "4", - "used_key_parts": ["l_shipDATE"], - "rows": 98, - "filtered": 100, - "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" - }, - "table": { - "table_name": "orders", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY", "i_o_totalprice"], - "key": "PRIMARY", + "key": "i_l_orderkey", "key_length": "4", - "used_key_parts": ["o_orderkey"], - "ref": ["dbt3_s001.lineitem.l_orderkey"], + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], "rowid_filter": { "range": { - "key": "i_o_totalprice", - "used_key_parts": ["o_totalprice"] + "key": "i_l_shipdate", + "used_key_parts": ["l_shipDATE"] }, - "rows": 69, - "selectivity_pct": 4.6 + "rows": 98, + "selectivity_pct": 1.632 }, - "rows": 1, - "filtered": 4.6, - "attached_condition": "orders.o_totalprice between 200000 and 230000" + "rows": 4, + "filtered": 1.632, + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" } } } @@ -388,8 +388,8 @@ FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND o_totalprice between 200000 and 230000; id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra -1 SIMPLE lineitem range PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_shipdate 4 NULL 98 98.00 100.00 100.00 Using index condition -1 SIMPLE orders eq_ref|filter PRIMARY,i_o_totalprice PRIMARY|i_o_totalprice 4|9 dbt3_s001.lineitem.l_orderkey 1 (5%) 0.11 (10%) 4.60 100.00 Using where; Using rowid filter +1 SIMPLE orders range PRIMARY,i_o_totalprice i_o_totalprice 9 NULL 69 71.00 100.00 100.00 Using index condition +1 SIMPLE lineitem ref|filter PRIMARY,i_l_shipdate,i_l_orderkey,i_l_orderkey_quantity i_l_orderkey|i_l_shipdate 4|4 dbt3_s001.orders.o_orderkey 4 (2%) 0.15 (2%) 1.63 100.00 Using where; Using rowid filter set statement optimizer_switch='rowid_filter=on' for ANALYZE FORMAT=JSON SELECT o_orderkey, l_linenumber, l_shipdate, o_totalprice FROM orders JOIN lineitem ON o_orderkey=l_orderkey WHERE l_shipdate BETWEEN '1997-01-01' AND '1997-01-31' AND @@ -401,53 +401,53 @@ ANALYZE "r_loops": 1, "r_total_time_ms": "REPLACED", "table": { - "table_name": "lineitem", + "table_name": "orders", "access_type": "range", - "possible_keys": [ - "PRIMARY", - "i_l_shipdate", - "i_l_orderkey", - "i_l_orderkey_quantity" - ], - "key": "i_l_shipdate", - "key_length": "4", - "used_key_parts": ["l_shipDATE"], + "possible_keys": ["PRIMARY", "i_o_totalprice"], + "key": "i_o_totalprice", + "key_length": "9", + "used_key_parts": ["o_totalprice"], "r_loops": 1, - "rows": 98, - "r_rows": 98, + "rows": 69, + "r_rows": 71, "r_total_time_ms": "REPLACED", "filtered": 100, "r_filtered": 100, - "index_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" + "index_condition": "orders.o_totalprice between 200000 and 230000" }, "table": { - "table_name": "orders", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY", "i_o_totalprice"], - "key": "PRIMARY", + "table_name": "lineitem", + "access_type": "ref", + "possible_keys": [ + "PRIMARY", + "i_l_shipdate", + "i_l_orderkey", + "i_l_orderkey_quantity" + ], + "key": "i_l_orderkey", "key_length": "4", - "used_key_parts": ["o_orderkey"], - "ref": ["dbt3_s001.lineitem.l_orderkey"], + "used_key_parts": ["l_orderkey"], + "ref": ["dbt3_s001.orders.o_orderkey"], "rowid_filter": { "range": { - "key": "i_o_totalprice", - "used_key_parts": ["o_totalprice"] + "key": "i_l_shipdate", + "used_key_parts": ["l_shipDATE"] }, - "rows": 69, - "selectivity_pct": 4.6, - "r_rows": 71, - "r_lookups": 96, - "r_selectivity_pct": 10.417, + "rows": 98, + "selectivity_pct": 1.632, + "r_rows": 98, + "r_lookups": 476, + "r_selectivity_pct": 2.3109, "r_buffer_size": "REPLACED", "r_filling_time_ms": "REPLACED" }, - "r_loops": 98, - "rows": 1, - "r_rows": 0.1122, + "r_loops": 71, + "rows": 4, + "r_rows": 0.1549, "r_total_time_ms": "REPLACED", - "filtered": 4.6, + "filtered": 1.632, "r_filtered": 100, - "attached_condition": "orders.o_totalprice between 200000 and 230000" + "attached_condition": "lineitem.l_shipDATE between '1997-01-01' and '1997-01-31'" } } } @@ -2072,7 +2072,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where -1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where +1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2 @@ -2097,6 +2097,14 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a1"], "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 115, + "selectivity_pct": 28.75 + }, "rows": 36, "filtered": 28.75, "attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2" @@ -2179,7 +2187,7 @@ test.t1 analyze status OK explain SELECT * FROM t1 WHERE a > 0 AND b=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 5 const 151 Using where +1 SIMPLE t1 ref|filter a,b b|a 5|5 const 151 (17%) Using where; Using rowid filter SELECT * FROM t1 WHERE a > 0 AND b=0; a b 1 0 @@ -2514,19 +2522,32 @@ ANALYZE "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["idx1", "idx2"], - "key": "idx1", - "key_length": "35", - "used_key_parts": ["nm"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["fl2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "idx1", + "used_key_parts": ["nm"] + }, + "rows": 44, + "selectivity_pct": 0.44, + "r_rows": 44, + "r_lookups": 1000, + "r_selectivity_pct": 0, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 1, - "rows": 44, - "r_rows": 44, + "rows": 921, + "r_rows": 0, "r_total_time_ms": "REPLACED", - "filtered": 9.21, - "r_filtered": 0, - "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'", - "attached_condition": "t1.fl2 = 0" + "filtered": 0.44, + "r_filtered": 100, + "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } } @@ -2559,19 +2580,31 @@ ANALYZE "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["idx1", "idx2"], - "key": "idx1", - "key_length": "35", - "used_key_parts": ["nm"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["fl2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "idx1", + "used_key_parts": ["nm"] + }, + "rows": 44, + "selectivity_pct": 0.44, + "r_rows": 0, + "r_lookups": 0, + "r_selectivity_pct": 0, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 1, - "rows": 44, + "rows": 911, "r_rows": 0, - "r_total_time_ms": "REPLACED", - "filtered": 9.11, + "filtered": 0.44, "r_filtered": 100, - "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'", - "attached_condition": "t1.fl2 = 0" + "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } } diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 1bf63d9..d7f1fe4 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1997,7 +1997,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1+1 = pk2+2 AND a1 = a2 ) WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where -1 PRIMARY t1 ref a1,b1 a1 5 test.t2.a2 36 28.75 Using where +1 PRIMARY t1 ref|filter a1,b1 a1|b1 5|4 test.t2.a2 36 (29%) 28.75 Using where; Using rowid filter 2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t1`.`pk1` + 1 = `test`.`t2`.`pk2` + 2 @@ -2022,6 +2022,14 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a1"], "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 115, + "selectivity_pct": 28.75 + }, "rows": 36, "filtered": 28.75, "attached_condition": "t1.b1 <= (subquery#2) and t1.pk1 + 1 = t2.pk2 + 2" @@ -2104,7 +2112,7 @@ test.t1 analyze status OK explain SELECT * FROM t1 WHERE a > 0 AND b=0; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref a,b b 5 const 128 Using where +1 SIMPLE t1 ref|filter a,b b|a 5|5 const 128 (14%) Using where; Using rowid filter SELECT * FROM t1 WHERE a > 0 AND b=0; a b 1 0 @@ -2439,19 +2447,32 @@ ANALYZE "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["idx1", "idx2"], - "key": "idx1", - "key_length": "35", - "used_key_parts": ["nm"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["fl2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "idx1", + "used_key_parts": ["nm"] + }, + "rows": 44, + "selectivity_pct": 0.44, + "r_rows": 44, + "r_lookups": 1000, + "r_selectivity_pct": 0, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 1, - "rows": 44, - "r_rows": 44, + "rows": 921, + "r_rows": 0, "r_total_time_ms": "REPLACED", - "filtered": 9.21, - "r_filtered": 0, - "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'", - "attached_condition": "t1.fl2 = 0" + "filtered": 0.44, + "r_filtered": 100, + "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } } @@ -2484,19 +2505,31 @@ ANALYZE "r_total_time_ms": "REPLACED", "table": { "table_name": "t1", - "access_type": "range", + "access_type": "ref", "possible_keys": ["idx1", "idx2"], - "key": "idx1", - "key_length": "35", - "used_key_parts": ["nm"], + "key": "idx2", + "key_length": "5", + "used_key_parts": ["fl2"], + "ref": ["const"], + "rowid_filter": { + "range": { + "key": "idx1", + "used_key_parts": ["nm"] + }, + "rows": 44, + "selectivity_pct": 0.44, + "r_rows": 0, + "r_lookups": 0, + "r_selectivity_pct": 0, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 1, - "rows": 44, + "rows": 911, "r_rows": 0, - "r_total_time_ms": "REPLACED", - "filtered": 9.11, + "filtered": 0.44, "r_filtered": 100, - "index_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'", - "attached_condition": "t1.fl2 = 0" + "attached_condition": "t1.nm like '3400%' or t1.nm like '3402%' or t1.nm like '3403%' or t1.nm like '3404%' or t1.nm like '3405%' or t1.nm like '3406%' or t1.nm like '3407%' or t1.nm like '3409%' or t1.nm like '3411%' or t1.nm like '3412%' or t1.nm like '3413%' or t1.nm like '3414%' or t1.nm like '3415%' or t1.nm like '3416%' or t1.nm like '3417%' or t1.nm like '3418%' or t1.nm like '3419%' or t1.nm like '3421%' or t1.nm like '3422%' or t1.nm like '3423%' or t1.nm like '3424%' or t1.nm like '3425%' or t1.nm like '3426%' or t1.nm like '3427%' or t1.nm like '3428%' or t1.nm like '3429%' or t1.nm like '3430%' or t1.nm like '3431%' or t1.nm like '3432%' or t1.nm like '3433%' or t1.nm like '3434%' or t1.nm like '3435%' or t1.nm like '3436%' or t1.nm like '3437%' or t1.nm like '3439%' or t1.nm like '3440%' or t1.nm like '3441%' or t1.nm like '3442%' or t1.nm like '3443%' or t1.nm like '3444%' or t1.nm like '3445%' or t1.nm like '3446%' or t1.nm like '3447%' or t1.nm like '3448%'" } } } @@ -2671,7 +2704,7 @@ count(*) 5 explain extended select count(*) from t1 where a between 21 and 30 and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref b,a b 5 const 24 9.60 Using where +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 24 (10%) 9.60 Using where; Using rowid filter Warnings: Note 1003 select count(0) AS `count(*)` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` between 21 and 30 select * from t1 where a between 21 and 30 and b=2; @@ -3133,7 +3166,7 @@ WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD('2017-01-30 08:24:51', INTERVAL -1 MONTH) ORDER BY timestamp DESC; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain 98 const 40 33.33 Using index condition; Using where; Using filesort +1 SIMPLE t1 ref|filter ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp 98|4 const 40 (33%) 33.33 Using index condition; Using where; Using filesort; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`domain` AS `domain`,`test`.`t1`.`registrant_name` AS `registrant_name`,`test`.`t1`.`registrant_organization` AS `registrant_organization`,`test`.`t1`.`registrant_street1` AS `registrant_street1`,`test`.`t1`.`registrant_street2` AS `registrant_street2`,`test`.`t1`.`registrant_street3` AS `registrant_street3`,`test`.`t1`.`registrant_street4` AS `registrant_street4`,`test`.`t1`.`registrant_street5` AS `registrant_street5`,`test`.`t1`.`registrant_city` AS `registrant_city`,`test`.`t1`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`t1`.`registrant_country` AS `registrant_country`,`test`.`t1`.`registrant_email` AS `registrant_email`,`test`.`t1`.`registrant_telephone` AS `registrant_telephone`,`test`.`t1`.`administrative_name` AS `administrative_name`,`test`.`t1`.`administrative_organization` AS `administrative_organization`,`test`.`t1`.`administrative_street1` AS `administrative_street1`,`test`.`t1`.`administrative_stree t2` AS `administrative_street2`,`test`.`t1`.`administrative_street3` AS `administrative_street3`,`test`.`t1`.`administrative_street4` AS `administrative_street4`,`test`.`t1`.`administrative_street5` AS `administrative_street5`,`test`.`t1`.`administrative_city` AS `administrative_city`,`test`.`t1`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`t1`.`administrative_country` AS `administrative_country`,`test`.`t1`.`administrative_email` AS `administrative_email`,`test`.`t1`.`administrative_telephone` AS `administrative_telephone`,`test`.`t1`.`technical_name` AS `technical_name`,`test`.`t1`.`technical_organization` AS `technical_organization`,`test`.`t1`.`technical_street1` AS `technical_street1`,`test`.`t1`.`technical_street2` AS `technical_street2`,`test`.`t1`.`technical_street3` AS `technical_street3`,`test`.`t1`.`technical_street4` AS `technical_street4`,`test`.`t1`.`technical_street5` AS `technical_street5`,`test`.`t1`.`technical_city` AS `technical_city`,`test `.`t1`.`technical_postal_code` AS `technical_postal_code`,`test`.`t1`.`technical_country` AS `technical_country`,`test`.`t1`.`technical_email` AS `technical_email`,`test`.`t1`.`technical_telephone` AS `technical_telephone`,`test`.`t1`.`json` AS `json`,`test`.`t1`.`timestamp` AS `timestamp` from `test`.`t1` where `test`.`t1`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`t1`.`timestamp` >= <cache>('2017-01-30 08:24:51' + interval -1 month) order by `test`.`t1`.`timestamp` desc SET optimizer_switch=@save_optimizer_switch; @@ -3382,7 +3415,7 @@ fi.fh in (6311439873746261694,-397087483897438286, id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where -1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where +1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using rowid filter Warnings: Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* @@ -3498,7 +3531,7 @@ fi.fh in (6311439873746261694,-397087483897438286, id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t index_merge PRIMARY,acli_rid,acli_tp acli_tp,acli_rid 2,767 NULL 2 100.00 Using intersect(acli_tp,acli_rid); Using where; Using index 1 SIMPLE a ref PRIMARY,acei_aclid acei_aclid 8 test.t.id 1 100.00 Using where; Using join buffer (flat, BKA join); Rowid-ordered scan -1 SIMPLE fi ref filt_aceid,filt_fh filt_aceid 8 test.a.id 24 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan +1 SIMPLE fi ref|filter filt_aceid,filt_fh filt_aceid|filt_fh 8|8 test.a.id 24 (14%) 14.46 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan; Using rowid filter Warnings: Note 1003 select `test`.`t`.`id` AS `id`,`test`.`fi`.`id` AS `id`,`test`.`fi`.`aceid` AS `aceid`,`test`.`fi`.`clid` AS `clid`,`test`.`fi`.`fh` AS `fh` from `test`.`acli` `t` join `test`.`acei` `a` join `test`.`filt` `fi` where `test`.`t`.`tp` = 121 and `test`.`a`.`atp` = 1 and `test`.`fi`.`aceid` = `test`.`a`.`id` and `test`.`a`.`aclid` = `test`.`t`.`id` and `test`.`t`.`rid` = 'B5FCC8C7111E4E3CBC21AAF5012F59C2' and `test`.`fi`.`fh` in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774) set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* @@ -3616,9 +3649,22 @@ ANALYZE "key_length": "8", "used_key_parts": ["aceid"], "ref": ["test.a.id"], + "rowid_filter": { + "range": { + "key": "filt_fh", + "used_key_parts": ["fh"] + }, + "rows": 81, + "selectivity_pct": 14.464, + "r_rows": 80, + "r_lookups": 80, + "r_selectivity_pct": 40, + "r_buffer_size": "REPLACED", + "r_filling_time_ms": "REPLACED" + }, "r_loops": 1, "rows": 24, - "r_rows": 80, + "r_rows": 32, "r_total_time_ms": "REPLACED", "filtered": 14.464, "r_filtered": 100 @@ -3628,7 +3674,7 @@ ANALYZE "join_type": "BKA", "mrr_type": "Rowid-ordered scan", "attached_condition": "fi.fh in (6311439873746261694,-397087483897438286,8518228073041491534,-5420422472375069774)", - "r_filtered": 40 + "r_filtered": 100 } } } @@ -3732,7 +3778,7 @@ WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 WHERE t2.i1 = t1.pk AND t2.i1 BETWEEN 3 AND 5); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 60 100.00 Using where -2 DEPENDENT SUBQUERY t2 ref c1,i1 i1 5 test.t1.pk 20 100.00 Using index condition; Using where +2 DEPENDENT SUBQUERY t2 ref|filter c1,i1 c1|i1 3|5 func 38 (25%) 25.00 Using where; Full scan on NULL key; Using rowid filter 2 DEPENDENT SUBQUERY a1 ALL NULL NULL NULL NULL 60 100.00 Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index fc3a290..6652b1a 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index e7c2e3e..cef6e0f 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -3755,7 +3755,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index fc3a290..6652b1a 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -3744,7 +3744,7 @@ EXPLAIN SELECT * FROM t1 WHERE ID_better=1 AND ID1_with_null IS NULL AND (ID2_with_null=1 OR ID2_with_null=2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref idx1,idx2 idx2 4 const 2 Using where +1 SIMPLE t1 ref|filter idx1,idx2 idx1|idx2 5|4 const 2 (1%) Using index condition; Using where; Using rowid filter DROP TABLE t1; CREATE TABLE t1 (a INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY ts(ts)); INSERT INTO t1 VALUES (30,"2006-01-03 23:00:00"), (31,"2006-01-03 23:00:00"); diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 4113779..7e32023 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1661,7 +1661,7 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` # gives selectivity data explain extended select * from t1 where a in (17,51,5) and b=2; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref b,a b 5 const 58 2.90 Using where +1 SIMPLE t1 ref|filter b,a b|a 5|5 const 58 (3%) 2.90 Using where; Using rowid filter Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` = 2 and `test`.`t1`.`a` in (17,51,5) drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 0f8ead4..350d43f 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7557,7 +7557,6 @@ best_access_path(JOIN *join, rec= MATCHING_ROWS_IN_OTHER_TABLE; // Fix for small tables Json_writer_object trace_access_idx(thd); - double eq_ref_rows= 0; /* full text keys require special treatment */ @@ -7596,8 +7595,7 @@ best_access_path(JOIN *join, type= JT_EQ_REF; trace_access_idx.add("access_type", join_type_str[type]) .add("index", keyinfo->name); - eq_ref_rows= tmp = prev_record_reads(join_positions, idx, - found_ref); + tmp = prev_record_reads(join_positions, idx, found_ref); records=1.0; } else @@ -7904,28 +7902,7 @@ best_access_path(JOIN *join, (s->table->file->index_flags(start_key->key,0,1) & HA_DO_RANGE_FILTER_PUSHDOWN)) { - double rows; - if (type == JT_EQ_REF) - { - /* - Treat EQ_REF access in a special way: - 1. We have no cost for index-only read. Assume its cost is 50% of - the cost of the full read. - - 2. A regular ref access will do #record_count lookups, but eq_ref - has "lookup cache" which reduces the number of lookups made. - The estimation code uses prev_record_reads() call to estimate: - - tmp = prev_record_reads(join_positions, idx, found_ref); - - Set the effective number of rows from "tmp" here. - */ - keyread_tmp= COST_ADD(eq_ref_rows / 2, s->startup_cost); - rows= eq_ref_rows; - } - else - rows= record_count * records; - + double rows= record_count * records; /* If we use filter F with selectivity s the the cost of fetching data by key using this filter will be @@ -7947,46 +7924,53 @@ best_access_path(JOIN *join, cost_of_fetching_1_row = tmp/rows cost_of_fetching_1_key_tuple = keyread_tmp/rows - access_cost_factor is the gain we expect for using rowid filter. - An access_cost_factor of 1.0 means that keyread_tmp is 0 - (using key read is infinitely fast) and the gain for each row when - using filter is great. - An access_cost_factor if 0.0 means that using keyread has the - same cost as reading rows, so there is no gain to get with - filter. - access_cost_factor should never be bigger than 1.0 (if all - calculations are correct) as the cost of keyread should always be - smaller than the cost of fetching the same number of keys + rows. - access_cost_factor should also never be smaller than 0.0. - The one exception is if number of records is 1 (eq_ref), then - because we are comparing rows to cost of keyread_tmp, keyread_tmp - is higher by 1.0. This is a big that will be fixed in a later - version. - - If we have limited the cost (=tmp) of reading rows with 'worst_seek' - we cannot use filters as the cost calculation below would cause - tmp to become negative. The future resultion is to not limit - cost with worst_seek. + Here's a more detailed explanation that uses the formulas behind + the function the call filter->get_adjusted_gain(). The function + takes as a parameter the number of probes/look-ups into the filter + that is equal to the number of fetched key entries that is equal to + the number of row fetches when no filter is used (assuming no + index condition pushdown is employed for the used key access). + Let this number be N. Then the total gain from using the filter is + N*a_adj - b where b is the cost of building the filter and + a_adj is calcilated as follows: + a - (1-access_cost_factor)*(1-s) = + (1+1_cond_eval_cost)*(1-s)-1_probe_cost - (1-access_cost_factor)*(1-s) + = (1-s)*(1_cond_eval_cost+access_cost_factor) - 1_probe_cost. + Here ((1-s)*(1_cond_eval_cost) * N is the gain from checking less + conditions pushed into the table, 1_probe_cost*N is the cost of the + probes and (1*s) * access_cost_factor * N must be the gain from + accessing less rows. + It does not matter how we calculate the cost of N full row fetches + cost_of_fetching_N_rows or + how we calculate the cost of fetching N key entries + cost_of_fetching_N_key_entries + the gain from less row fetches will be + (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) * (1-s) + and this should be equal to (1*s) * access_cost_factor * N. + Thus access_cost_factor must be calculated as + (cost_of_fetching_N_rows - cost_of_fetching_N_key_entries) / N. + + For safety we clip cost_of_fetching_N_key_entries by the value + of cost_of_fethching_N_row though formally it's not necessary. */ - double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0); - if (!(records < s->worst_seeks && - records <= thd->variables.max_seeks_for_key)) - trace_access_idx.add("rowid_filter_skipped", "worst/max seeks clipping"); - else if (access_cost_factor <= 0.0) - trace_access_idx.add("rowid_filter_skipped", "cost_factor <= 0"); - else + /* + For eq_ref access we assume that the cost of fetching N key entries + is equal to the half of fetching N rows + */ + double key_access_cost= + type == JT_EQ_REF ? 0.5 * tmp : MY_MIN(tmp, keyread_tmp); + double access_cost_factor= MY_MIN((tmp - key_access_cost) / rows, 1.0); + + filter= + table->best_range_rowid_filter_for_partial_join(start_key->key, + rows, + access_cost_factor); + if (filter) { - filter= - table->best_range_rowid_filter_for_partial_join(start_key->key, - rows, - access_cost_factor); - if (filter) - { - tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); - DBUG_ASSERT(tmp >= 0); - trace_access_idx.add("rowid_filter_key", + tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); + DBUG_ASSERT(tmp >= 0); + trace_access_idx.add("rowid_filter_key", s->table->key_info[filter->key_no].name); - } } } trace_access_idx.add("rows", records).add("cost", tmp); @@ -8139,19 +8123,19 @@ best_access_path(JOIN *join, uint key_no= s->quick->index; /* See the comment concerning using rowid filter for with ref access */ - keyread_tmp= s->table->quick_index_only_costs[key_no] * record_count; - double access_cost_factor= MY_MIN((rows - keyread_tmp) / rows, 1.0); - if (access_cost_factor > 0.0) + double row_access_cost= s->quick->read_time * record_count; + double key_access_cost= + MY_MIN(row_access_cost, + s->table->quick_index_only_costs[key_no] * record_count); + double access_cost_factor= MY_MIN((row_access_cost - key_access_cost) / + rows, 1.0); + filter= + s->table->best_range_rowid_filter_for_partial_join(key_no, rows, + access_cost_factor); + if (filter) { - filter= - s->table-> - best_range_rowid_filter_for_partial_join(key_no, rows, - access_cost_factor); - if (filter) - { - tmp-= filter->get_adjusted_gain(rows); - DBUG_ASSERT(tmp >= 0); - } + tmp-= filter->get_adjusted_gain(rows); + DBUG_ASSERT(tmp >= 0); } else trace_access_scan.add("rowid_filter_skipped", "cost_factor <= 0");