#At file:///home/gluh/MySQL/mysql-6.0-opt/ based on revid:sergefp@stripped
2707 Sergey Glukhov 2009-01-13
WL#4424 Full index condition pushdown with batched key access join(3rd ver)
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
per-file messages:
mysql-test/r/join_cache.result
result fix, test result
mysql-test/r/join_nested_jcl6.result
result fix
mysql-test/r/subselect3_jcl6.result
result fix
mysql-test/r/subselect_sj2_jcl6.result
result fix
mysql-test/t/join_cache.test
test case
sql/handler.cc
check if an index tuple matches the index condition
sql/handler.h
added skip_index_tuple() callback function which
checks if the record combination matches the index condition,
used in BKA, BKA_UNIQUE
sql/opt_range.cc
added initialization of new member
sql/sql_join_cache.cc
added bka_skip_index_tuple(), bka_unique_skip_index_tuple().
Functions are used for the 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.
sql/sql_select.cc
Changes:
check_join_cache_usage() func
returns cache level,
added icp_other_tables_ok parameter
push_index_cond() func
added options parameter(for EXPLAIN)
sql/sql_select.h
JOIN_TAB class:
added member 'cache_idx_cond'
JOIN_CACHE class:
added member 'pushed_idx_cond'
JOIN_CACHE_BNL,JOIN_CACHE_BKA classes:
added initialization of 'pushed_index_cond'
JOIN_CACHE_BKA,JOIN_CACHE_BKA_UNIQUE:
added new method skip_index_tuple(range_seq_t rseq, char *range_info);
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result 2008-12-07 20:44:24 +0000
+++ b/mysql-test/r/join_cache.result 2009-01-13 13:22:47 +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
@@ -3576,3 +3576,72 @@ AVG(c)
set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1, t2;
+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-13 13:22:47 +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-08 21:15:06 +0000
+++ b/mysql-test/r/subselect3_jcl6.result 2009-01-13 13:22:47 +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-11-05 00:53:38 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result 2009-01-13 13:22:47 +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; Start temporary
1 PRIMARY t2 eq_ref PRIMARY,Population PRIMARY 3 test.t1.Country 1 Using where; End temporary; Using join buffer
-1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 1 Using index condition; Using where; Using join buffer
+1 PRIMARY t3 eq_ref PRIMARY,Percentage PRIMARY 33 test.t2.Code,const 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 2008-12-07 20:44:24 +0000
+++ b/mysql-test/t/join_cache.test 2009-01-13 13:22:47 +0000
@@ -976,3 +976,53 @@ set join_buffer_size=default;
set join_cache_level=default;
DROP TABLE t1, t2;
+
+#
+# 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-14 11:36:15 +0000
+++ b/sql/handler.cc 2009-01-13 13:22:47 +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 2008-12-14 11:36:15 +0000
+++ b/sql/handler.h 2009-01-13 13:22:47 +0000
@@ -1201,7 +1201,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-16 11:29:22 +0000
+++ b/sql/opt_range.cc 2009-01-13 13:22:47 +0000
@@ -7511,7 +7511,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];
@@ -8429,7 +8429,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 2008-12-11 00:21:13 +0000
+++ b/sql/sql_join_cache.cc 2009-01-13 13:22:47 +0000
@@ -813,6 +813,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(!cache->pushed_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
@@ -2146,7 +2222,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,
+ pushed_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);
@@ -2877,6 +2955,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 (cache->pushed_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
@@ -2923,7 +3081,9 @@ JOIN_CACHE_BKA_UNIQUE::join_matching_rec
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,
+ pushed_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 2008-12-24 20:41:33 +0000
+++ b/sql/sql_select.cc 2009-01-13 13:22:47 +0000
@@ -8235,12 +8235,14 @@ Item *make_cond_remainder(Item *cond, bo
in tab->select_cond
keyno Index for which extract and push the condition
other_tbls_ok TRUE <=> Fields of other non-const tables are allowed
+ options Join's options (checking for SELECT_DESCRIBE)
DESCRIPTION
Try to extract and push the index condition down to table handler
*/
-static void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok)
+static void push_index_cond(JOIN_TAB *tab, uint keyno, bool other_tbls_ok,
+ ulonglong options)
{
DBUG_ENTER("push_index_cond");
Item *idx_cond;
@@ -8258,9 +8260,33 @@ 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)))
+ {
+ if ((options & SELECT_DESCRIBE))
+ tab->cache_idx_cond= idx_cond;
+ else
+ tab->cache_idx_cond= tab->cache->pushed_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
@@ -8422,10 +8448,12 @@ void revise_cache_usage(JOIN_TAB *join_t
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 returns TRUE if condition pushdown supports
+ other tables presence
DESCRIPTION
The function finds out whether the table 'tab' can be joined using a join
@@ -8476,14 +8504,15 @@ void revise_cache_usage(JOIN_TAB *join_t
results in that no join buffer is used to join the table.
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;
@@ -8493,11 +8522,12 @@ 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)
- return FALSE;
+ return 0;
if (i == join->const_tables)
- return FALSE;
+ return 0;
if (options & SELECT_NO_JOIN_CACHE)
goto no_join_cache;
if (tab->use_quick == 2)
@@ -8548,17 +8578,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;
@@ -8567,14 +8599,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 : ;
}
@@ -8582,7 +8612,7 @@ bool check_join_cache_usage(JOIN_TAB *ta
no_join_cache:
if (cache_level>2)
revise_cache_usage(tab);
- return FALSE;
+ return 0;
}
@@ -8620,10 +8650,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.
@@ -8644,13 +8676,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)
{
@@ -8658,7 +8689,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, options);
break;
case JT_EQ_REF:
table->status=STATUS_NO_RECORD;
@@ -8671,13 +8702,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)
{
@@ -8685,7 +8715,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, options);
break;
case JT_REF_OR_NULL:
case JT_REF:
@@ -8697,11 +8727,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)
{
@@ -8713,7 +8743,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)
{
@@ -8721,7 +8750,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, options);
break;
case JT_FT:
table->status=STATUS_NO_RECORD;
@@ -8734,11 +8763,11 @@ make_join_readinfo(JOIN *join, ulonglong
If the incoming data set is already sorted don't use cache.
*/
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)
@@ -8810,10 +8839,10 @@ 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,
+ options);
}
break;
default:
@@ -19258,6 +19287,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 2008-12-14 11:36:15 +0000
+++ b/sql/sql_select.h 2009-01-13 13:22:47 +0000
@@ -216,6 +216,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: */
@@ -666,6 +670,9 @@ public:
JOIN_CACHE *prev_cache;
/* Pointer to the next join cache if there is any */
JOIN_CACHE *next_cache;
+ /* Pointer to the condition, used in BKA only*/
+ Item *pushed_idx_cond;
+
/* Shall initialize the join cache structure */
virtual int init()=0;
@@ -745,6 +752,7 @@ public:
join= j;
join_tab= tab;
prev_cache= next_cache= 0;
+ pushed_idx_cond= 0;
}
/*
@@ -761,6 +769,7 @@ public:
next_cache= 0;
if (prev)
prev->next_cache= this;
+ pushed_idx_cond= 0;
}
/* Initialize the BNL cache */
@@ -832,7 +841,8 @@ public:
join= j;
join_tab= tab;
prev_cache= next_cache= 0;
- mrr_mode= flags;
+ mrr_mode= flags;
+ pushed_idx_cond= 0;
}
/*
@@ -851,6 +861,7 @@ public:
if (prev)
prev->next_cache= this;
mrr_mode= flags;
+ pushed_idx_cond= 0;
}
/* Initialize the BKA cache */
@@ -859,8 +870,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);
};
/*
@@ -1148,7 +1161,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);
};
