diff options
| author | Sergei Petrunia <psergey@askmonty.org> | 2019-08-29 15:37:49 +0300 |
|---|---|---|
| committer | Sergei Petrunia <psergey@askmonty.org> | 2019-08-30 12:02:40 +0300 |
| commit | ef76f81c982bdbcfa4797ce26224db9c016ddebd (patch) | |
| tree | 2f65a45620e011e24ddd4fd80d2eae35a28c4f7a /mysql-test/main/subselect_mat.result | |
| parent | d58437d1956b2fd92197beac2e9c869ef968eba7 (diff) | |
| download | mariadb-git-ef76f81c982bdbcfa4797ce26224db9c016ddebd.tar.gz | |
MDEV-20109: Optimizer ignores distinct key created for materialized...
(Backported to 10.3, addressed review input)
Sj_materialization_picker::check_qep(): fix error in cost/fanout
calculations:
- for each join prefix, add #prefix_rows / TIME_FOR_COMPARE to the cost,
like best_extension_by_limited_search does
- Remove the fanout produced by the subquery tables.
- Also take into account join condition selectivity
optimize_wo_join_buffering() (used by LooseScan and FirstMatch)
- also add #prefix_rows / TIME_FOR_COMPARE to the cost of each prefix.
- Also take into account join condition selectivity
Diffstat (limited to 'mysql-test/main/subselect_mat.result')
| -rw-r--r-- | mysql-test/main/subselect_mat.result | 16 |
1 files changed, 8 insertions, 8 deletions
diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 45f6f135b48..d38b40586b0 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -1477,8 +1477,8 @@ set @@optimizer_switch=@optimizer_switch_local_default; SET @@optimizer_switch='semijoin=on,materialization=on'; EXPLAIN SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 7 func,func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 SELECT COUNT(*) FROM t1 WHERE (f1,f2) IN (SELECT f1,f2 FROM t2); COUNT(*) @@ -2404,8 +2404,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 hash_ALL NULL #hash#$hj 4 test.t2.i1 9 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 9 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2417,8 +2417,8 @@ alter table t1 add key(id); explain SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT t2.i1 FROM t2 WHERE t2.i1 = t2.i2); 1 @@ -2474,8 +2474,8 @@ INSERT INTO t2 VALUES (11,11),(12,12),(13,13); CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 -1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +1 PRIMARY t1 index id id 4 NULL 9 Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); 1 |