diff options
| -rwxr-xr-x[-rw-r--r--] | debian/mariadb-plugin-mroonga.prerm | 2 | ||||
| -rw-r--r-- | mysql-test/main/subselect_sj2_mat.result | 12 | ||||
| -rw-r--r-- | mysql-test/main/subselect_sj_jcl6.result | 8 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 17 | ||||
| -rw-r--r-- | storage/innobase/mtr/mtr0mtr.cc | 197 |
5 files changed, 96 insertions, 140 deletions
diff --git a/debian/mariadb-plugin-mroonga.prerm b/debian/mariadb-plugin-mroonga.prerm index b64ea064142..cdd26ebbc45 100644..100755 --- a/debian/mariadb-plugin-mroonga.prerm +++ b/debian/mariadb-plugin-mroonga.prerm @@ -2,7 +2,7 @@ set -e -# Install Mroonga +# Uninstall Mroonga mysql --defaults-file=/etc/mysql/debian.cnf < /usr/share/mysql/mroonga/uninstall.sql || true # Always exit with success instead of leaving dpkg in a broken state diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index 7ccaed50251..589144f1238 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1933,16 +1933,18 @@ AND t3.id_product IN (SELECT id_product FROM t2 t2_5 WHERE t2_5.id_t2 = 29 OR t2 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 12 1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t2_2.id_product 1 Using where; Using index -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where -1 PRIMARY t2_3 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary -1 PRIMARY t2_5 ref id_t2,id_product id_product 5 test.t3.id_product 44 Using index condition; Using where; Start temporary; End temporary +1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t5 ALL NULL NULL NULL NULL 18 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery6> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 8 test.t3.id_product,const 1 Using where; Using index -1 PRIMARY <subquery5> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 Using where +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 func 1 Using where 1 PRIMARY t1 index NULL PRIMARY 8 NULL 73 Using where; Using index; Using join buffer (flat, BNL join) 3 MATERIALIZED t2_2 ref id_t2,id_product id_t2 5 const 12 Using where -2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 5 MATERIALIZED t2_4 range id_t2,id_product id_t2 5 NULL 18 Using index condition; Using where +6 MATERIALIZED t2_5 range id_t2,id_product id_t2 5 NULL 31 Using index condition; Using where +2 MATERIALIZED t2_1 ref id_t2,id_product id_t2 5 const 51 +4 MATERIALIZED t2_3 range id_t2,id_product id_t2 5 NULL 33 Using index condition; Using where set optimizer_switch='rowid_filter=default'; drop table t1,t2,t3,t4,t5; set global innodb_stats_persistent= @innodb_stats_persistent_save; diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 7ba13e5b59f..02e9a833db6 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3536,8 +3536,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) @@ -3550,8 +3550,8 @@ EXPLAIN SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) GROUP BY a HAVING a != 'z'; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 Using temporary; Using filesort -1 PRIMARY t ref idx_a idx_a 4 test.t2.b 2 Using index +1 PRIMARY t range idx_a idx_a 4 NULL 3 Using where; Using index +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 Using where 2 MATERIALIZED t1 ref idx_a idx_a 4 test.t2.b 2 Using index SELECT a FROM t1 t WHERE a IN (SELECT b FROM t1, t2 WHERE b = a) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index f0d6fc74242..ededfc88988 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -3144,7 +3144,22 @@ bool Sj_materialization_picker::check_qep(JOIN *join, *strategy= SJ_OPT_MATERIALIZE_SCAN; *read_time= prefix_cost; - *record_count= prefix_rec_count / mat_info->rows_with_duplicates; + /* + Note: the next line means we did not remove the subquery's fanout from + *record_count. It needs to be removed, as the join prefix is + + ntX SJM-SCAN(it1 ... itN) | (ot1 ... otN) ... + + here, the SJM-SCAN may have introduced subquery's fanout (duplicate rows, + rows that don't have matches in ot1_i). All this fanout is gone after + table otN (or earlier) but taking it into account is hard. + + Some consolation here is that SJM-Scan strategy is applicable when the + subquery is smaller than tables otX. If the subquery has large cardinality, + we can greatly overestimate *record_count here, but it doesn't matter as + SJ-Materialization-Lookup is a better strategy anyway. + */ + *record_count= prefix_rec_count; *handled_fanout= mat_nest->sj_inner_tables; if (unlikely(join->thd->trace_started())) { diff --git a/storage/innobase/mtr/mtr0mtr.cc b/storage/innobase/mtr/mtr0mtr.cc index dbaae5e72bd..792c5d711b7 100644 --- a/storage/innobase/mtr/mtr0mtr.cc +++ b/storage/innobase/mtr/mtr0mtr.cc @@ -31,8 +31,6 @@ Created 11/26/1995 Heikki Tuuri #include "fsp0sysspace.h" #include "page0types.h" #include "mtr0log.h" -#include "log0log.h" - #include "log0recv.h" /** Iterate over a memo block in reverse. */ @@ -204,143 +202,84 @@ private: /** Release latches and decrement the buffer fix count. @param slot memo slot */ -static -void -memo_slot_release(mtr_memo_slot_t* slot) -{ - switch (slot->type) { - case MTR_MEMO_BUF_FIX: - case MTR_MEMO_PAGE_S_FIX: - case MTR_MEMO_PAGE_SX_FIX: - case MTR_MEMO_PAGE_X_FIX: { - - buf_block_t* block; - - block = reinterpret_cast<buf_block_t*>(slot->object); - - block->unfix(); - buf_page_release_latch(block, slot->type); - break; - } - - case MTR_MEMO_S_LOCK: - rw_lock_s_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - break; - - case MTR_MEMO_SX_LOCK: - rw_lock_sx_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - break; - - case MTR_MEMO_X_LOCK: - rw_lock_x_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - break; - -#ifdef UNIV_DEBUG - default: - ut_ad(slot->type == MTR_MEMO_MODIFY); -#endif /* UNIV_DEBUG */ - } - - slot->object = NULL; -} - -/** Unfix a page, do not release the latches on the page. -@param slot memo slot */ -static -void -memo_block_unfix(mtr_memo_slot_t* slot) +static void memo_slot_release(mtr_memo_slot_t *slot) { - switch (slot->type) { - case MTR_MEMO_BUF_FIX: - case MTR_MEMO_PAGE_S_FIX: - case MTR_MEMO_PAGE_X_FIX: - case MTR_MEMO_PAGE_SX_FIX: { - reinterpret_cast<buf_block_t*>(slot->object)->unfix(); - break; - } - - case MTR_MEMO_S_LOCK: - case MTR_MEMO_X_LOCK: - case MTR_MEMO_SX_LOCK: - break; -#ifdef UNIV_DEBUG - default: -#endif /* UNIV_DEBUG */ - break; - } -} -/** Release latches represented by a slot. -@param slot memo slot */ -static -void -memo_latch_release(mtr_memo_slot_t* slot) -{ - switch (slot->type) { - case MTR_MEMO_BUF_FIX: - case MTR_MEMO_PAGE_S_FIX: - case MTR_MEMO_PAGE_SX_FIX: - case MTR_MEMO_PAGE_X_FIX: { - buf_block_t* block; - - block = reinterpret_cast<buf_block_t*>(slot->object); - - memo_block_unfix(slot); - - buf_page_release_latch(block, slot->type); - - slot->object = NULL; - break; - } - - case MTR_MEMO_S_LOCK: - rw_lock_s_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - slot->object = NULL; - break; - - case MTR_MEMO_X_LOCK: - rw_lock_x_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - slot->object = NULL; - break; - - case MTR_MEMO_SX_LOCK: - rw_lock_sx_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); - slot->object = NULL; - break; - + switch (slot->type) { #ifdef UNIV_DEBUG - default: - ut_ad(slot->type == MTR_MEMO_MODIFY); - - slot->object = NULL; + default: + ut_ad(!"invalid type"); + break; + case MTR_MEMO_MODIFY: + break; #endif /* UNIV_DEBUG */ - } + case MTR_MEMO_S_LOCK: + rw_lock_s_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_SX_LOCK: + rw_lock_sx_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_X_LOCK: + rw_lock_x_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_BUF_FIX: + case MTR_MEMO_PAGE_S_FIX: + case MTR_MEMO_PAGE_SX_FIX: + case MTR_MEMO_PAGE_X_FIX: + buf_block_t *block= reinterpret_cast<buf_block_t*>(slot->object); + block->unfix(); + buf_page_release_latch(block, slot->type); + break; + } + slot->object= NULL; } /** Release the latches acquired by the mini-transaction. */ struct ReleaseLatches { - - /** @return true always. */ - bool operator()(mtr_memo_slot_t* slot) const - { - if (slot->object != NULL) { - memo_latch_release(slot); - } - - return(true); - } + /** @return true always. */ + bool operator()(mtr_memo_slot_t *slot) const + { + if (!slot->object) + return true; + switch (slot->type) { +#ifdef UNIV_DEBUG + default: + ut_ad(!"invalid type"); + break; + case MTR_MEMO_MODIFY: + break; +#endif /* UNIV_DEBUG */ + case MTR_MEMO_S_LOCK: + rw_lock_s_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_X_LOCK: + rw_lock_x_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_SX_LOCK: + rw_lock_sx_unlock(reinterpret_cast<rw_lock_t*>(slot->object)); + break; + case MTR_MEMO_BUF_FIX: + case MTR_MEMO_PAGE_S_FIX: + case MTR_MEMO_PAGE_SX_FIX: + case MTR_MEMO_PAGE_X_FIX: + buf_block_t *block= reinterpret_cast<buf_block_t*>(slot->object); + block->unfix(); + buf_page_release_latch(block, slot->type); + break; + } + slot->object= NULL; + return true; + } }; /** Release the latches and blocks acquired by the mini-transaction. */ struct ReleaseAll { - /** @return true always. */ - bool operator()(mtr_memo_slot_t* slot) const - { - if (slot->object != NULL) { - memo_slot_release(slot); - } - - return(true); - } + /** @return true always. */ + bool operator()(mtr_memo_slot_t *slot) const + { + if (slot->object) + memo_slot_release(slot); + return true; + } }; #ifdef UNIV_DEBUG @@ -349,7 +288,7 @@ struct DebugCheck { /** @return true always. */ bool operator()(const mtr_memo_slot_t* slot) const { - ut_a(slot->object == NULL); + ut_ad(!slot->object); return(true); } }; |