Below is the list of changes that have just been committed into a local
6.0 repository of gluh. When gluh does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-06-27 20:02:38+05:00, gluh@stripped +8 -0
WL#4424 Full index condition pushdown with batched key access join(3rd ver)
added index condition pushdown for BKA[_UNIQUE] join
mysql-test/r/join_cache.result@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +50 -8
result fix, test result
mysql-test/r/join_nested_jcl6.result@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +2 -2
result fix
mysql-test/t/join_cache.test@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +34 -0
test case
sql/handler.cc@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +5 -0
check if an index tuple matches the index condition
sql/handler.h@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +14 -1
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@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +2 -2
added initialization of new member
sql/sql_select.cc@stripped, 2008-06-27 20:02:36+05:00, gluh@stripped +196 -26
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.h@stripped, 2008-06-27 20:02:37+05:00, gluh@stripped +9 -1
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);
diff -Nrup a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result
--- a/mysql-test/r/join_cache.result 2008-06-07 03:02:56 +05:00
+++ b/mysql-test/r/join_cache.result 2008-06-27 20:02:36 +05:00
@@ -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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; 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 8 Using where; Using join buffer
+1 SIMPLE City ref Country Country 3 world.Country.Code 8 Using index condition; Using where; Using join buffer
SELECT City.Name, Country.Name, CountryLanguage.Language
FROM City,Country,CountryLanguage
WHERE City.Country=Country.Code AND
@@ -3185,3 +3185,45 @@ a1<>a2 a1 a2 b2 b3 c3 s1 s2
0 6 6 73 73 738
0 6 6 74 74 749
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=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; 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; Using join buffer
+drop table t1,t2;
diff -Nrup a/mysql-test/r/join_nested_jcl6.result b/mysql-test/r/join_nested_jcl6.result
--- a/mysql-test/r/join_nested_jcl6.result 2008-04-06 11:35:11 +05:00
+++ b/mysql-test/r/join_nested_jcl6.result 2008-06-27 20:02:36 +05:00
@@ -1453,12 +1453,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; 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; 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
diff -Nrup a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test
--- a/mysql-test/t/join_cache.test 2008-06-03 06:45:25 +05:00
+++ b/mysql-test/t/join_cache.test 2008-06-27 20:02:36 +05:00
@@ -750,3 +750,37 @@ SELECT a1<>a2, a1, a2, b2, b3, c3,
FROM t1,t2,t3 WHERE a1=a2 AND b2=b3 AND MOD(c3,10)>7;
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=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;
+
+drop table t1,t2;
diff -Nrup a/sql/handler.cc b/sql/handler.cc
--- a/sql/handler.cc 2008-06-17 09:57:31 +05:00
+++ b/sql/handler.cc 2008-06-27 20:02:36 +05:00
@@ -4432,6 +4432,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);
diff -Nrup a/sql/handler.h b/sql/handler.h
--- a/sql/handler.h 2008-06-12 09:27:19 +05:00
+++ b/sql/handler.h 2008-06-27 20:02:36 +05:00
@@ -1123,7 +1123,20 @@ 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
diff -Nrup a/sql/opt_range.cc b/sql/opt_range.cc
--- a/sql/opt_range.cc 2008-06-03 06:44:19 +05:00
+++ b/sql/opt_range.cc 2008-06-27 20:02:36 +05:00
@@ -7416,7 +7416,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];
@@ -8330,7 +8330,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);
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc 2008-06-12 09:27:19 +05:00
+++ b/sql/sql_select.cc 2008-06-27 20:02:36 +05:00
@@ -8074,9 +8074,21 @@ 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->cache && tab->cache->is_key_access() &&
+ (idx_cond->used_tables() &
+ ~(tab->table->map | tab->join->const_table_map)))
+ 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
@@ -8203,7 +8215,7 @@ void revise_cache_usage(JOIN_TAB *join_t
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)
{
@@ -8216,9 +8228,9 @@ bool check_join_cache_usage(JOIN_TAB *ta
uint i= tab-join->join_tab;
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)
@@ -8249,12 +8261,12 @@ bool check_join_cache_usage(JOIN_TAB *ta
(tab->cache ||
(tab->cache= new JOIN_CACHE_BNL(join, tab, prev_cache))) &&
!tab->cache->init())
- return TRUE;
+ return cache_level;
goto no_join_cache;
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;
@@ -8270,7 +8282,7 @@ bool check_join_cache_usage(JOIN_TAB *ta
(tab->cache= new JOIN_CACHE_BKA_UNIQUE(join, tab, flags, prev_cache))
) &&
!tab->cache->init()))
- return TRUE;
+ return cache_level;
goto no_join_cache;
default : ;
}
@@ -8278,7 +8290,7 @@ bool check_join_cache_usage(JOIN_TAB *ta
no_join_cache:
if (cache_level>2)
revise_cache_usage(tab);
- return FALSE;
+ return 0;
}
/*
@@ -8315,10 +8327,11 @@ 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;
/*
TODO: don't always instruct first table's ref/range access method to
produce sorted output.
@@ -8360,13 +8373,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;
+ icp_other_tables_ok= TRUE;
if (check_join_cache_usage(tab, join, options, no_jbuf_after))
{
- 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)
{
@@ -8374,7 +8386,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:
@@ -8386,10 +8398,10 @@ make_join_readinfo(JOIN *join, ulonglong
}
delete tab->quick;
tab->quick=0;
- using_join_cache= FALSE;
+ icp_other_tables_ok= TRUE;
if (check_join_cache_usage(tab, join, options, no_jbuf_after))
{
- using_join_cache= TRUE;
+ tab->use_join_cache= TRUE;
tab[-1].next_select=sub_select_cache;
}
if (tab->type == JT_REF)
@@ -8403,7 +8415,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)
{
@@ -8411,7 +8422,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;
@@ -8424,11 +8435,12 @@ 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;
+ icp_other_tables_ok= TRUE;
if (check_join_cache_usage(tab, join, options, no_jbuf_after))
{
- using_join_cache= TRUE;
- tab[-1].next_select=sub_select_cache;
+ tab->use_join_cache= TRUE;
+ icp_other_tables_ok= FALSE;
+ tab[-1].next_select=sub_select_cache;
}
/* These init changes read_record */
if (tab->use_quick == 2)
@@ -8497,10 +8509,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 &&
+ 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:
@@ -18575,6 +18586,81 @@ bool bka_range_seq_skip_record(range_seq
DBUG_RETURN(res);
}
+
+/*
+ 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));
+}
+
/*
Using BKA find matches from the next table for records from the join buffer
@@ -18638,7 +18724,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);
@@ -19370,6 +19458,86 @@ bool bka_unique_range_seq_skip_record(ra
/*
+ 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
SYNOPSIS
@@ -19414,7 +19582,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);
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h 2008-06-11 20:57:53 +05:00
+++ b/sql/sql_select.h 2008-06-27 20:02:37 +05:00
@@ -661,6 +661,8 @@ 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;
@@ -740,6 +742,7 @@ public:
join= j;
join_tab= tab;
prev_cache= next_cache= 0;
+ pushed_idx_cond= 0;
}
/*
@@ -828,6 +831,7 @@ public:
join_tab= tab;
prev_cache= next_cache= 0;
mrr_mode= flags;
+ pushed_idx_cond= 0;
}
/*
@@ -846,6 +850,7 @@ public:
if (prev)
prev->next_cache= this;
mrr_mode= flags;
+ pushed_idx_cond= 0;
}
/* Initialize the BKA cache */
@@ -855,7 +860,8 @@ public:
/* Shall get the key built over the next record from the join buffer */
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);
};
/*
@@ -1144,6 +1150,8 @@ public:
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 |
|---|
| • bk commit into 6.0 tree (gluh:1.2638) WL#4424 | gluh | 27 Jun |
