2807 Sergey Glukhov 2009-01-14
WL#4424 Full index condition pushdown with batched key access join
added index condition pushdown for BKA[_UNIQUE] join
modified:
mysql-test/r/join_cache.result
mysql-test/r/join_nested_jcl6.result
mysql-test/r/subselect3_jcl6.result
mysql-test/r/subselect_sj2_jcl6.result
mysql-test/t/join_cache.test
sql/handler.cc
sql/handler.h
sql/opt_range.cc
sql/sql_join_cache.cc
sql/sql_select.cc
sql/sql_select.h
2806 Igor Babaev 2009-01-11
Fixed bug #42020.
When an outer join is executed using a join buffer the buffer
is scanned twice. At the first scan matches for records in the
buffer are looked for and any record for which a match is found
is marked. At the second scan the records without matches are
looked through and each of them is complemented by null columns
after having been read into the record buffer. Records with
matches are just skipped and not even read into the join buffer.
As a result at the end of the scan the record in the record buffer
is not guaranteed to be the last record from join buffer. This
can break the whole nested loop process and lead to wrong
results produced by the operation.
All fields of the last record from the join buffer must be restored
in the corresponding record buffers after the second scan.
modified:
mysql-test/r/join_cache.result
mysql-test/t/join_cache.test
sql/sql_join_cache.cc
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result 2009-01-11 22:06:01 +0000
+++ b/mysql-test/r/join_cache.result 2009-01-14 10:29:36 +0000
@@ -867,7 +867,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1174,7 +1174,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1481,7 +1481,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -1788,7 +1788,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2099,7 +2099,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2313,7 +2313,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2527,7 +2527,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -2741,7 +2741,7 @@ CountryLanguage.Percentage > 50;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE CountryLanguage ALL PRIMARY,Percentage NULL NULL NULL 984 Using where
1 SIMPLE Country eq_ref PRIMARY PRIMARY 3 world.CountryLanguage.Country 1 Using where; Using join buffer
-1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 18 Using index condition(BKA); Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -3718,3 +3718,72 @@ a a a b b val
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1,t2,t3;
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2));
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+(2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+(4,4, 'qwerty');
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+(2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+(4,4, 'qwerty');
+set join_cache_level=5;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+f1 f2 f3
+1 1 qwerty
+2 2 qwerty
+1 1 qwerty
+2 2 qwerty
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
+set join_cache_level=6;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+f1 f2 f3
+1 1 qwerty
+2 2 qwerty
+1 1 qwerty
+2 2 qwerty
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
+set join_cache_level=7;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+f1 f2 f3
+1 1 qwerty
+2 2 qwerty
+1 1 qwerty
+2 2 qwerty
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
+set join_cache_level=8;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+f1 f2 f3
+1 1 qwerty
+2 2 qwerty
+1 1 qwerty
+2 2 qwerty
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 3 Using index condition(BKA); Using join buffer
+drop table t1,t2;
+set join_cache_level=default;
=== modified file 'mysql-test/r/join_nested_jcl6.result'
--- a/mysql-test/r/join_nested_jcl6.result 2008-10-09 00:31:34 +0000
+++ b/mysql-test/r/join_nested_jcl6.result 2009-01-14 10:29:36 +0000
@@ -1451,12 +1451,12 @@ id select_type table type possible_keys
1 SIMPLE t2 ALL NULL NULL NULL NULL X
1 SIMPLE t3 ref a a 5 test.t2.b X Using join buffer
1 SIMPLE t5 ref a a 5 test.t3.b X Using join buffer
-1 SIMPLE t4 ref a a 5 test.t3.b X Using where; Using join buffer
+1 SIMPLE t4 ref a a 5 test.t3.b X Using index condition(BKA); Using join buffer
explain select * from (t4 join t6 on t6.a=t4.b) right join t3 on t4.a=t3.b
join t2 left join (t5 join t7 on t7.a=t5.b) on t5.a=t2.b where t3.a<=>t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL X
-1 SIMPLE t3 ref a a 5 test.t2.b X Using where; Using join buffer
+1 SIMPLE t3 ref a a 5 test.t2.b X Using index condition(BKA); Using join buffer
1 SIMPLE t4 ref a a 5 test.t3.b X Using join buffer
1 SIMPLE t6 ref a a 5 test.t4.b X Using join buffer
1 SIMPLE t5 ref a a 5 test.t2.b X Using join buffer
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result 2008-12-28 15:00:21 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2009-01-14 10:29:36 +0000
@@ -728,7 +728,7 @@ WHERE t3.name='xxx' AND t2.id=t3.id);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using where
2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Using index; Full scan on NULL key
-2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using where; Full scan on NULL key; Using join buffer
+2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 func 1 Using index condition(BKA); Using where; Full scan on NULL key; Using join buffer
SELECT * FROM t1
WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
WHERE t3.name='xxx' AND t2.id=t3.id);
=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result 2008-12-22 19:03:25 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2009-01-14 10:29:36 +0000
@@ -306,7 +306,7 @@ t2.Population > 100000);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 range Population,Country Population 4 NULL 1 Using index condition; Using MRR
1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t1.Country,const 1 Using index condition; Using where; Using join buffer
-1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using where; Using join buffer
+1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t3.Country 1 Using index condition(BKA); Using where; Using join buffer
DROP TABLE t1,t2,t3;
CREATE TABLE t1 (
Code char(3) NOT NULL DEFAULT '',
=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test 2009-01-11 22:06:01 +0000
+++ b/mysql-test/t/join_cache.test 2009-01-14 10:29:36 +0000
@@ -1091,3 +1091,53 @@ set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1,t2,t3;
+
+#
+# WL#4424 Full index condition pushdown with batched key access join
+#
+create table t1(f1 int, f2 int);
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2(f1 int not null, f2 int not null, f3 char(200), key(f1,f2));
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+ (2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+ (4,4, 'qwerty');
+insert into t2 values (1,1, 'qwerty'),(1,2, 'qwerty'),(1,3, 'qwerty');
+insert into t2 values (2,1, 'qwerty'),(2,2, 'qwerty'),(2,3, 'qwerty'),
+ (2,4, 'qwerty'),(2,5, 'qwerty');
+insert into t2 values (3,1, 'qwerty'),(3,4, 'qwerty');
+insert into t2 values (4,1, 'qwerty'),(4,2, 'qwerty'),(4,3, 'qwerty'),
+ (4,4, 'qwerty');
+
+set join_cache_level=5;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+
+set join_cache_level=6;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+
+set join_cache_level=7;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+
+set join_cache_level=8;
+select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t1.f2 and t2.f2 + 1 >= t1.f1 + 1;
+
+explain select t2.f1, t2.f2, t2.f3 from t1,t2
+where t1.f1=t2.f1 and t2.f2 between t1.f1 and t2.f2;
+
+drop table t1,t2;
+set join_cache_level=default;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2008-12-31 13:49:36 +0000
+++ b/sql/handler.cc 2009-01-14 10:29:36 +0000
@@ -4498,6 +4498,11 @@ int DsMrr_impl::dsmrr_fill_buffer(handle
while ((rowids_buf_cur < rowids_buf_end) &&
!(res= h2->handler::multi_range_read_next(&range_info)))
{
+ KEY_MULTI_RANGE *curr_range= &h2->handler::mrr_cur_range;
+ if (h2->mrr_funcs.skip_index_tuple &&
+ h2->mrr_funcs.skip_index_tuple(h2->mrr_iter, curr_range->ptr))
+ continue;
+
/* Put rowid, or {rowid, range_id} pair into the buffer */
h2->position(table->record[0]);
memcpy(rowids_buf_cur, h2->ref, h2->ref_length);
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2009-01-09 16:38:52 +0000
+++ b/sql/handler.h 2009-01-14 10:29:36 +0000
@@ -1198,7 +1198,19 @@ typedef struct st_range_seq_if
0 - The record shall be left in the stream
*/
bool (*skip_record) (range_seq_t seq, char *range_info, uchar *rowid);
-
+
+ /*
+ Check if the record combination matches the index condition
+ SYNOPSIS
+ skip_index_tuple()
+ seq The value returned by RANGE_SEQ_IF::init()
+ range_info Information about the next range
+
+ RETURN
+ 0 - The record combination satisfies the index condition
+ 1 - Otherwise
+ */
+ bool (*skip_index_tuple) (range_seq_t seq, char *range_info);
} RANGE_SEQ_IF;
class COST_VECT
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2008-12-31 13:49:36 +0000
+++ b/sql/opt_range.cc 2009-01-14 10:29:36 +0000
@@ -7516,7 +7516,7 @@ ha_rows check_quick_select(PARAM *param,
uint *mrr_flags, uint *bufsize, COST_VECT *cost)
{
SEL_ARG_RANGE_SEQ seq;
- RANGE_SEQ_IF seq_if = {sel_arg_range_seq_init, sel_arg_range_seq_next, 0};
+ RANGE_SEQ_IF seq_if = {sel_arg_range_seq_init, sel_arg_range_seq_next, 0, 0};
handler *file= param->table->file;
ha_rows rows;
uint keynr= param->real_keynr[idx];
@@ -8434,7 +8434,7 @@ int QUICK_RANGE_SELECT::reset()
if (sorted)
mrr_flags |= HA_MRR_SORTED;
- RANGE_SEQ_IF seq_funcs= {quick_range_seq_init, quick_range_seq_next, 0};
+ RANGE_SEQ_IF seq_funcs= {quick_range_seq_init, quick_range_seq_next, 0, 0};
error= file->multi_range_read_init(&seq_funcs, (void*)this, ranges.elements,
mrr_flags, mrr_buf_desc? mrr_buf_desc:
&empty_buf);
=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc 2009-01-11 22:06:01 +0000
+++ b/sql/sql_join_cache.cc 2009-01-14 10:29:36 +0000
@@ -803,6 +803,82 @@ uint JOIN_CACHE_BKA::aux_buffer_incr()
return incr;
}
+
+/*
+ Check if the record combination matches the index condition
+
+ SYNOPSIS
+ JOIN_CACHE_BKA::skip_index_tuple()
+ rseq Value returned by bka_range_seq_init()
+ range_info MRR range association data
+
+ DESCRIPTION
+ This function is invoked from MRR implementation to check if an index
+ tuple matches the index condition. It is used in the case where the index
+ condition actually depends on both columns of the used index and columns
+ from previous tables.
+
+ Accessing columns of the previous tables requires special handling with
+ BKA. The idea of BKA is to collect record combinations in a buffer and
+ then do a batch of ref access lookups, i.e. by the time we're doing a
+ lookup its previous-records-combination is not in prev_table->record[0]
+ but somewhere in the join buffer.
+
+ We need to get it from there back into prev_table(s)->record[0] before we
+ can evaluate the index condition, and that's why we need this function
+ instead of regular IndexConditionPushdown.
+
+ NOTE
+ Possible optimization:
+ Before we unpack the record from a previous table
+ check if this table is used in the condition.
+ If so then unpack the record otherwise skip the unpacking.
+ This should be done by a special virtual method
+ get_partial_record_by_pos().
+
+ RETURN
+ 0 The record combination satisfies the index condition
+ 1 Otherwise
+*/
+
+bool JOIN_CACHE_BKA::skip_index_tuple(range_seq_t rseq, char *range_info)
+{
+ DBUG_ENTER("JOIN_CACHE_BKA::skip_index_tuple");
+ JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq;
+ cache->get_record_by_pos((uchar*)range_info);
+ DBUG_RETURN(!join_tab->cache_idx_cond->val_int());
+}
+
+
+/*
+ Check if the record combination matches the index condition
+
+ SYNOPSIS
+ bka_skip_index_tuple()
+ rseq Value returned by bka_range_seq_init()
+ range_info MRR range association data
+
+ DESCRIPTION
+ This is wrapper for JOIN_CACHE_BKA::skip_index_tuple method,
+ see comments there.
+
+ NOTE
+ This function is used as a RANGE_SEQ_IF::skip_index_tuple callback.
+
+ RETURN
+ 0 The record combination satisfies the index condition
+ 1 Otherwise
+*/
+
+static
+bool bka_skip_index_tuple(range_seq_t rseq, char *range_info)
+{
+ DBUG_ENTER("bka_skip_index_tuple");
+ JOIN_CACHE_BKA *cache= (JOIN_CACHE_BKA *) rseq;
+ DBUG_RETURN(cache->skip_index_tuple(rseq, range_info));
+}
+
+
/*
Write record fields and their required offsets into the join cache buffer
@@ -2149,7 +2225,9 @@ enum_nested_loop_state JOIN_CACHE_BKA::j
RANGE_SEQ_IF seq_funcs= { bka_range_seq_init,
bka_range_seq_next,
check_only_first_match ?
- bka_range_seq_skip_record : 0 };
+ bka_range_seq_skip_record : 0,
+ join_tab->cache_idx_cond ?
+ bka_skip_index_tuple : 0 };
/* The value of skip_last must be always FALSE when this function is called */
DBUG_ASSERT(!skip_last);
@@ -2889,6 +2967,86 @@ bool bka_unique_range_seq_skip_record(ra
DBUG_RETURN(res);
}
+
+/*
+ Check if the record combination matches the index condition
+
+ SYNOPSIS
+ JOIN_CACHE_BKA_UNIQUE::skip_index_tuple()
+ rseq Value returned by bka_range_seq_init()
+ range_info MRR range association data
+
+ DESCRIPTION
+ See JOIN_CACHE_BKA::skip_index_tuple().
+ This function is the variant for use with
+ JOIN_CACHE_BKA_UNIQUE. The difference from JOIN_CACHE_BKA case is that
+ there may be multiple previous table record combinations that share the
+ same key, i.e. they map to the same MRR range.
+ As a consequence, we need to loop through all previous table record
+ combinations that match the given MRR range key range_info until we find
+ one that satisfies the index condition.
+
+ NOTE
+ Possible optimization:
+ Before we unpack the record from a previous table
+ check if this table is used in the condition.
+ If so then unpack the record otherwise skip the unpacking.
+ This should be done by a special virtual method
+ get_partial_record_by_pos().
+
+ RETURN
+ 0 The record combination satisfies the index condition
+ 1 Otherwise
+
+
+*/
+
+bool JOIN_CACHE_BKA_UNIQUE::skip_index_tuple(range_seq_t rseq, char *range_info)
+{
+ DBUG_ENTER("JOIN_CACHE_BKA_UNIQUE::skip_index_tuple");
+ JOIN_CACHE_BKA_UNIQUE *cache= (JOIN_CACHE_BKA_UNIQUE *) rseq;
+ uchar *last_rec_ref_ptr= cache->get_next_rec_ref((uchar*) range_info);
+ uchar *next_rec_ref_ptr= last_rec_ref_ptr;
+ do
+ {
+ next_rec_ref_ptr= cache->get_next_rec_ref(next_rec_ref_ptr);
+ uchar *rec_ptr= next_rec_ref_ptr + cache->rec_fields_offset;
+ cache->get_record_by_pos(rec_ptr);
+ if (join_tab->cache_idx_cond->val_int())
+ DBUG_RETURN(FALSE);
+ } while(next_rec_ref_ptr != last_rec_ref_ptr);
+ DBUG_RETURN(TRUE);
+}
+
+
+/*
+ Check if the record combination matches the index condition
+
+ SYNOPSIS
+ bka_unique_skip_index_tuple()
+ rseq Value returned by bka_range_seq_init()
+ range_info MRR range association data
+
+ DESCRIPTION
+ This is wrapper for JOIN_CACHE_BKA_UNIQUE::skip_index_tuple method,
+ see comments there.
+
+ NOTE
+ This function is used as a RANGE_SEQ_IF::skip_index_tuple callback.
+
+ RETURN
+ 0 The record combination satisfies the index condition
+ 1 Otherwise
+*/
+
+static
+bool bka_unique_skip_index_tuple(range_seq_t rseq, char *range_info)
+{
+ DBUG_ENTER("bka_unique_skip_index_tuple");
+ JOIN_CACHE_BKA_UNIQUE *cache= (JOIN_CACHE_BKA_UNIQUE *) rseq;
+ DBUG_RETURN(cache->skip_index_tuple(rseq, range_info));
+}
+
/*
Using BKA_UNIQUE find matches from the next table for records from join buffer
@@ -2931,11 +3089,12 @@ JOIN_CACHE_BKA_UNIQUE::join_matching_rec
bool no_association= test(mrr_mode & HA_MRR_NO_ASSOCIATION);
/* Set functions to iterate over keys in the join buffer */
-
RANGE_SEQ_IF seq_funcs= { bka_unique_range_seq_init,
bka_unique_range_seq_next,
check_only_first_match && !no_association ?
- bka_unique_range_seq_skip_record : 0 };
+ bka_unique_range_seq_skip_record : 0,
+ join_tab->cache_idx_cond ?
+ bka_unique_skip_index_tuple : 0 };
/* The value of skip_last must be always FALSE when this function is called */
DBUG_ASSERT(!skip_last);
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-01-10 10:56:50 +0000
+++ b/sql/sql_select.cc 2009-01-14 10:29:36 +0000
@@ -9223,9 +9223,28 @@ static void push_index_cond(JOIN_TAB *ta
if (idx_cond)
{
+ Item *idx_remainder_cond= 0;
tab->pre_idx_push_select_cond= tab->select_cond;
- Item *idx_remainder_cond=
- tab->table->file->idx_cond_push(keyno, idx_cond);
+
+ /*
+ For BKA cache we store condition to special BKA cache field
+ because evaluation of the condition requires additional operations
+ before the evaluation. This condition is used in
+ JOIN_CACHE_BKA[_UNIQUE]::skip_index_tuple() functions.
+ */
+ if (tab->use_join_cache &&
+ /*
+ if cache is used then the value is TRUE only
+ for BKA[_UNIQUE] cache (see check_join_cache_usage func).
+ In this case other_tbls_ok is an equivalent of
+ cache->is_key_access().
+ */
+ other_tbls_ok &&
+ (idx_cond->used_tables() &
+ ~(tab->table->map | tab->join->const_table_map)))
+ tab->cache_idx_cond= idx_cond;
+ else
+ idx_remainder_cond= tab->table->file->idx_cond_push(keyno, idx_cond);
/*
Disable eq_ref's "lookup cache" if we've pushed down an index
@@ -9725,10 +9744,12 @@ bool setup_sj_materialization(JOIN_TAB *
SYNOPSIS
check_join_cache_usage()
- tab joined table to check join buffer usage for
- join join for which the check is performed
- options options of the join
- no_jbuf_after don't use join buffering after table with this number
+ tab joined table to check join buffer usage for
+ join join for which the check is performed
+ options options of the join
+ no_jbuf_after don't use join buffering after table with this number
+ icp_other_tables_ok OUT TRUE if condition pushdown supports
+ other tables presence
DESCRIPTION
The function finds out whether the table 'tab' can be joined using a join
@@ -9803,14 +9824,15 @@ bool setup_sj_materialization(JOIN_TAB *
#endif
RETURN
- TRUE if a join buffer can be employed to join the table 'tab'
- FALSE otherwise
+
+ cache level if cache is used, otherwise returns 0
*/
static
-bool check_join_cache_usage(JOIN_TAB *tab,
+uint check_join_cache_usage(JOIN_TAB *tab,
JOIN *join, ulonglong options,
- uint no_jbuf_after)
+ uint no_jbuf_after,
+ bool *icp_other_tables_ok)
{
uint flags;
COST_VECT cost;
@@ -9820,9 +9842,10 @@ bool check_join_cache_usage(JOIN_TAB *ta
uint cache_level= join->thd->variables.join_cache_level;
bool force_unlinked_cache= test(cache_level & 1);
uint i= tab - join->join_tab;
-
+
+ *icp_other_tables_ok= TRUE;
if (cache_level == 0 || i == join->const_tables)
- return FALSE;
+ return 0;
if (options & SELECT_NO_JOIN_CACHE)
goto no_join_cache;
@@ -9889,17 +9912,19 @@ bool check_join_cache_usage(JOIN_TAB *ta
if (cache_level <= 2 && (tab->first_inner || tab->first_sj_inner_tab))
goto no_join_cache;
if ((options & SELECT_DESCRIBE) ||
- (tab->cache ||
- (tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
+ ((tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
!tab->cache->init())
- return TRUE;
+ {
+ *icp_other_tables_ok= FALSE;
+ return cache_level;
+ }
goto no_join_cache;
case JT_SYSTEM:
case JT_CONST:
case JT_REF:
case JT_EQ_REF:
if (cache_level <= 4)
- return FALSE;
+ return 0;
flags= HA_MRR_NO_NULL_ENDPOINTS;
if (tab->table->covering_keys.is_set(tab->ref.key))
flags|= HA_MRR_INDEX_ONLY;
@@ -9908,14 +9933,12 @@ bool check_join_cache_usage(JOIN_TAB *ta
if ((rows != HA_POS_ERROR) && !(flags & HA_MRR_USE_DEFAULT_IMPL) &&
(!(flags & HA_MRR_NO_ASSOCIATION) || cache_level > 6) &&
((options & SELECT_DESCRIBE) ||
- (tab->cache ||
- cache_level <= 6 &&
+ (cache_level <= 6 &&
(tab->cache= new JOIN_CACHE_BKA(join, tab, flags, prev_cache)) ||
cache_level > 6 &&
(tab->cache= new JOIN_CACHE_BKA_UNIQUE(join, tab, flags, prev_cache))
- ) &&
- !tab->cache->init()))
- return TRUE;
+ ) && !tab->cache->init()))
+ return cache_level;
goto no_join_cache;
default : ;
}
@@ -9923,7 +9946,7 @@ bool check_join_cache_usage(JOIN_TAB *ta
no_join_cache:
if (cache_level>2)
revise_cache_usage(tab);
- return FALSE;
+ return 0;
}
@@ -9967,10 +9990,12 @@ make_join_readinfo(JOIN *join, ulonglong
{
JOIN_TAB *tab=join->join_tab+i;
TABLE *table=tab->table;
- bool using_join_cache;
+ bool icp_other_tables_ok;
tab->read_record.table= table;
tab->read_record.file=table->file;
tab->next_select=sub_select; /* normal select */
+ tab->use_join_cache= FALSE;
+ tab->cache_idx_cond= 0;
/*
TODO: don't always instruct first table's ref/range access method to
produce sorted output.
@@ -10005,13 +10030,12 @@ make_join_readinfo(JOIN *join, ulonglong
tab->read_first_record= tab->type == JT_SYSTEM ?
join_read_system :join_read_const;
tab->read_record.read_record= join_no_more_records;
- using_join_cache= FALSE;
- if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+ if (check_join_cache_usage(tab, join, options, no_jbuf_after,
+ &icp_other_tables_ok))
{
- using_join_cache= TRUE;
+ tab->use_join_cache= TRUE;
tab[-1].next_select=sub_select_cache;
}
- tab->use_join_cache= using_join_cache;
if (table->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
@@ -10019,7 +10043,7 @@ make_join_readinfo(JOIN *join, ulonglong
table->file->extra(HA_EXTRA_KEYREAD);
}
else
- push_index_cond(tab, tab->ref.key, !using_join_cache);
+ push_index_cond(tab, tab->ref.key, icp_other_tables_ok);
break;
case JT_EQ_REF:
table->status=STATUS_NO_RECORD;
@@ -10032,13 +10056,12 @@ make_join_readinfo(JOIN *join, ulonglong
tab->quick=0;
tab->read_first_record= join_read_key;
tab->read_record.read_record= join_no_more_records;
- using_join_cache= FALSE;
- if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+ if (check_join_cache_usage(tab, join, options, no_jbuf_after,
+ &icp_other_tables_ok))
{
- using_join_cache= TRUE;
+ tab->use_join_cache= TRUE;
tab[-1].next_select=sub_select_cache;
}
- tab->use_join_cache= using_join_cache;
if (table->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
@@ -10046,7 +10069,7 @@ make_join_readinfo(JOIN *join, ulonglong
table->file->extra(HA_EXTRA_KEYREAD);
}
else
- push_index_cond(tab, tab->ref.key, !using_join_cache);
+ push_index_cond(tab, tab->ref.key, icp_other_tables_ok);
break;
case JT_REF_OR_NULL:
case JT_REF:
@@ -10058,11 +10081,11 @@ make_join_readinfo(JOIN *join, ulonglong
}
delete tab->quick;
tab->quick=0;
- using_join_cache= FALSE;
- if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+ if (check_join_cache_usage(tab, join, options, no_jbuf_after,
+ &icp_other_tables_ok))
{
- using_join_cache= TRUE;
- tab[-1].next_select=sub_select_cache;
+ tab->use_join_cache= TRUE;
+ tab[-1].next_select=sub_select_cache;
}
if (tab->type == JT_REF)
{
@@ -10074,7 +10097,6 @@ make_join_readinfo(JOIN *join, ulonglong
tab->read_first_record= join_read_always_key_or_null;
tab->read_record.read_record= join_read_next_same_or_null;
}
- tab->use_join_cache= using_join_cache;
if (table->covering_keys.is_set(tab->ref.key) &&
!table->no_keyread)
{
@@ -10082,7 +10104,7 @@ make_join_readinfo(JOIN *join, ulonglong
table->file->extra(HA_EXTRA_KEYREAD);
}
else
- push_index_cond(tab, tab->ref.key, !using_join_cache);
+ push_index_cond(tab, tab->ref.key, icp_other_tables_ok);
break;
case JT_FT:
table->status=STATUS_NO_RECORD;
@@ -10097,11 +10119,11 @@ make_join_readinfo(JOIN *join, ulonglong
materialization nest.
*/
table->status=STATUS_NO_RECORD;
- using_join_cache= FALSE;
- if (check_join_cache_usage(tab, join, options, no_jbuf_after))
+ if (check_join_cache_usage(tab, join, options, no_jbuf_after,
+ &icp_other_tables_ok))
{
- using_join_cache= TRUE;
- tab[-1].next_select=sub_select_cache;
+ tab->use_join_cache= TRUE;
+ tab[-1].next_select=sub_select_cache;
}
/* These init changes read_record */
if (tab->use_quick == 2)
@@ -10173,10 +10195,9 @@ make_join_readinfo(JOIN *join, ulonglong
tab->type=JT_NEXT; // Read with index_first / index_next
}
}
- tab->use_join_cache= using_join_cache;
if (tab->select && tab->select->quick &&
tab->select->quick->index != MAX_KEY && ! tab->table->key_read)
- push_index_cond(tab, tab->select->quick->index, !using_join_cache);
+ push_index_cond(tab, tab->select->quick->index, icp_other_tables_ok);
}
break;
default:
@@ -21500,6 +21521,8 @@ void select_describe(JOIN *join, bool ne
if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
table->file->pushed_idx_cond)
extra.append(STRING_WITH_LEN("; Using index condition"));
+ else if (tab->cache_idx_cond)
+ extra.append(STRING_WITH_LEN("; Using index condition(BKA)"));
if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION ||
quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT ||
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-01-08 05:47:10 +0000
+++ b/sql/sql_select.h 2009-01-14 10:29:36 +0000
@@ -218,6 +218,10 @@ typedef struct st_join_table
TABLE_REF ref;
bool use_join_cache;
JOIN_CACHE *cache;
+ /*
+ Index condition for BKA access join
+ */
+ Item *cache_idx_cond;
SQL_SELECT *cache_select;
JOIN *join;
/* SemiJoinDuplicateElimination variables: */
@@ -832,7 +836,7 @@ public:
join= j;
join_tab= tab;
prev_cache= next_cache= 0;
- mrr_mode= flags;
+ mrr_mode= flags;
}
/*
@@ -859,8 +863,10 @@ public:
bool is_key_access() { return TRUE; }
/* Shall get the key built over the next record from the join buffer */
- virtual uint get_next_key(uchar **key);
+ virtual uint get_next_key(uchar **key);
+ /* Check if the record combination matches the index condition */
+ bool skip_index_tuple(range_seq_t rseq, char *range_info);
};
/*
@@ -1149,7 +1155,9 @@ public:
return get_next_rec_ref(curr_key_entry+key_entry_length-
get_size_of_rec_offset());
}
-
+
+ /* Check if the record combination matches the index condition */
+ bool skip_index_tuple(range_seq_t rseq, char *range_info);
};
| Thread |
|---|
| • bzr push into mysql-6.0-opt branch (Sergey.Glukhov:2806 to 2807)WL#4424 | Sergey Glukhov | 14 Jan |
