#At file:///home/tnurnberg/forest/mysql-6.0-bka-preview/
2648 Tatiana A. Nurnberg 2008-09-22
WL#4421 Add hints on join buffer usage for join queries
Adds USE/IGNORE/FORCE JOIN_BUFFER[(<size>[,<level>])]
modified:
mysql-test/r/group_by.result
mysql-test/r/join_cache.result
mysql-test/t/group_by.test
mysql-test/t/join_cache.test
sql/lex.h
sql/sql_lex.h
sql/sql_select.cc
sql/sql_select.h
sql/sql_yacc.yy
sql/table.h
per-file messages:
mysql-test/r/group_by.result
Test neglected to drop a table, confusing follow-up
tests if they were run in a particular order.
mysql-test/r/join_cache.result
adds tests for the JOIN_BUFFER() hints.
mysql-test/t/group_by.test
Test neglected to drop a table, confusing follow-up
tests if they were run in a particular order.
mysql-test/t/join_cache.test
adds tests for the JOIN_BUFFER() hints.
sql/lex.h
new keyword JOIN_BUFFER
sql/sql_lex.h
getters/setters for JOIN_BUFFER-related hints on
st_select_lex
sql/sql_select.cc
If a size-hint is given for JOIN_BUFFER, use it for
the cache instead of sysvar. Likewise for cache-level
hint and IGNORE/FORCE/USE. If we cannot get buffering
at the requested level for a given table, decrease
level until we succeed (or reach 0).
sql/sql_select.h
Set on cache what level it is (the level we actually
got, not the one the user asked for).
Adds helper get_buffer_size() that returns the size
requested for this join_buffer (specifically, or the
sysvar value otherwise).
sql/sql_yacc.yy
parse JOIN_BUFFER hints
sql/table.h
define JOIN_BUFFER hints. set them on table.
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2008-04-01 15:13:57 +0000
+++ b/mysql-test/r/group_by.result 2008-09-22 12:29:33 +0000
@@ -1624,7 +1624,7 @@ b
NULL
1
2
-DROP TABLE t1;
+DROP TABLE t1,t2;
CREATE TABLE t1 ( a INT, b INT );
SELECT b c, (SELECT a FROM t1 WHERE b = c)
FROM t1;
=== modified file 'mysql-test/r/join_cache.result'
--- a/mysql-test/r/join_cache.result 2008-07-22 04:54:18 +0000
+++ b/mysql-test/r/join_cache.result 2008-09-22 12:29:33 +0000
@@ -796,6 +796,33 @@ join_buffer_size 131072
show variables like 'join_cache_level';
Variable_name Value
join_cache_level 1
+JOIN_BUFFER(256) must give the same results as join_buffer_size=256
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(256)
+WHERE City.Country=Country.Code AND
+Country.Name LIKE 'L%' AND City.Population > 100000;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE Country ALL NULL NULL NULL NULL 239 Using where
+1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(256)
+WHERE City.Country=Country.Code AND
+Country.Name LIKE 'L%' AND City.Population > 100000;
+Name Name
+Vientiane Laos
+Riga Latvia
+Daugavpils Latvia
+Maseru Lesotho
+Beirut Lebanon
+Tripoli Lebanon
+Monrovia Liberia
+Tripoli Libyan Arab Jamahiriya
+Bengasi Libyan Arab Jamahiriya
+Misrata Libyan Arab Jamahiriya
+Vilnius Lithuania
+Kaunas Lithuania
+Klaipeda Lithuania
+?iauliai Lithuania
+Panevezys Lithuania
DROP DATABASE world;
CREATE DATABASE world;
use world;
@@ -3053,6 +3080,52 @@ PRIMARY KEY (genreid)
CREATE INDEX t11_masterclip ON t11( masterclip);
INSERT INTO t11 VALUES
(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y');
+JOIN_BUFFER(DEFAULT,6) must give the same results as join_cache_level=6
+set join_cache_level=default;
+show variables like 'join_cache_level';
+Variable_name Value
+join_cache_level 1
+EXPLAIN
+SELECT t1.uniquekey, t1.xml AS affiliateXml,
+t8.name AS artistName, t8.artistid,
+t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
+t10.subgenreid, t10.name AS subgenreName,
+t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
+t4.priority + t5.priority + t6.priority AS overallPriority,
+t3.path AS path, t3.mediaid,
+t4.formatid, t4.name AS formatName,
+t5.formatclassid, t5.name AS formatclassName,
+t6.formattypeid, t6.name AS formattypeName
+FROM t1 FORCE JOIN_BUFFER(DEFAULT,6),
+t2 FORCE JOIN_BUFFER(DEFAULT,6),
+t3 FORCE JOIN_BUFFER(DEFAULT,6),
+t4 FORCE JOIN_BUFFER(DEFAULT,6),
+t5 FORCE JOIN_BUFFER(DEFAULT,6),
+t6 FORCE JOIN_BUFFER(DEFAULT,6),
+t7 FORCE JOIN_BUFFER(DEFAULT,6),
+t8 FORCE JOIN_BUFFER(DEFAULT,6),
+t9 FORCE JOIN_BUFFER(DEFAULT,6),
+t10 FORCE JOIN_BUFFER(DEFAULT,6),
+t11 FORCE JOIN_BUFFER(DEFAULT,6)
+WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
+t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND
+t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND
+t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
+t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND
+t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
+t1.metaid = t2.metaid AND t1.affiliateid = '2';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t6 system PRIMARY NULL NULL NULL 1
+1 SIMPLE t1 ref t1_affiliateid,t1_metaid t1_affiliateid 4 const 1
+1 SIMPLE t4 ref PRIMARY,t4_formatclassid,t4_formats_idx t4_formats_idx 1 const 1 Using index condition; Using where; Using join buffer
+1 SIMPLE t5 eq_ref PRIMARY,t5_formattypeid PRIMARY 4 test.t4.formatclassid 1 Using where; Using join buffer
+1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using join buffer
+1 SIMPLE t7 ref PRIMARY PRIMARY 4 test.t1.metaid 1 Using index
+1 SIMPLE t3 ref t3_metaid,t3_formatid,t3_metaidformatid t3_metaid 4 test.t1.metaid 2 Using where; Using join buffer
+1 SIMPLE t8 eq_ref PRIMARY PRIMARY 4 test.t7.artistid 1 Using join buffer
+1 SIMPLE t9 index PRIMARY,t9_subgenreid,t9_metaid PRIMARY 8 NULL 2 Using where; Using index; Using join buffer
+1 SIMPLE t10 eq_ref PRIMARY,t10_genreid PRIMARY 4 test.t9.subgenreid 1 Using join buffer
+1 SIMPLE t11 eq_ref PRIMARY PRIMARY 4 test.t10.genreid 1 Using join buffer
set join_cache_level=6;
EXPLAIN
SELECT t1.uniquekey, t1.xml AS affiliateXml,
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2008-04-01 15:13:57 +0000
+++ b/mysql-test/t/group_by.test 2008-09-22 12:29:33 +0000
@@ -1062,7 +1062,7 @@ SELECT a from t2 GROUP BY a;
EXPLAIN SELECT b from t2 GROUP BY b;
SELECT b from t2 GROUP BY b;
-DROP TABLE t1;
+DROP TABLE t1,t2;
#
# Bug #31797: error while parsing subqueries -- WHERE is parsed as HAVING
=== modified file 'mysql-test/t/join_cache.test'
--- a/mysql-test/t/join_cache.test 2008-07-06 20:22:50 +0000
+++ b/mysql-test/t/join_cache.test 2008-09-22 12:29:33 +0000
@@ -140,6 +140,22 @@ set join_buffer_size=default;
show variables like 'join_buffer_size';
show variables like 'join_cache_level';
+
+
+### WL4421 -- Add hints on join buffer usage for join queries
+
+--echo JOIN_BUFFER(256) must give the same results as join_buffer_size=256
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(256)
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(256)
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+
DROP DATABASE world;
@@ -652,6 +668,43 @@ CREATE INDEX t11_masterclip ON t11( mast
INSERT INTO t11 VALUES
(19, "Pop & Dance", 95, 'Y'), (3, "Rock & Alternative", 100, 'Y');
+
+### WL4421 -- Add hints on join buffer usage for join queries
+
+--echo JOIN_BUFFER(DEFAULT,6) must give the same results as join_cache_level=6
+
+set join_cache_level=default;
+show variables like 'join_cache_level';
+EXPLAIN
+SELECT t1.uniquekey, t1.xml AS affiliateXml,
+ t8.name AS artistName, t8.artistid,
+ t11.name AS genreName, t11.genreid, t11.priority AS genrePriority,
+ t10.subgenreid, t10.name AS subgenreName,
+ t2.name AS metaName, t2.metaid, t2.xml AS metaXml,
+ t4.priority + t5.priority + t6.priority AS overallPriority,
+ t3.path AS path, t3.mediaid,
+ t4.formatid, t4.name AS formatName,
+ t5.formatclassid, t5.name AS formatclassName,
+ t6.formattypeid, t6.name AS formattypeName
+FROM t1 FORCE JOIN_BUFFER(DEFAULT,6),
+ t2 FORCE JOIN_BUFFER(DEFAULT,6),
+ t3 FORCE JOIN_BUFFER(DEFAULT,6),
+ t4 FORCE JOIN_BUFFER(DEFAULT,6),
+ t5 FORCE JOIN_BUFFER(DEFAULT,6),
+ t6 FORCE JOIN_BUFFER(DEFAULT,6),
+ t7 FORCE JOIN_BUFFER(DEFAULT,6),
+ t8 FORCE JOIN_BUFFER(DEFAULT,6),
+ t9 FORCE JOIN_BUFFER(DEFAULT,6),
+ t10 FORCE JOIN_BUFFER(DEFAULT,6),
+ t11 FORCE JOIN_BUFFER(DEFAULT,6)
+WHERE t7.metaid = t2.metaid AND t7.artistid = t8.artistid AND
+ t9.metaid = t2.metaid AND t9.subgenreid = t10.subgenreid AND
+ t10.genreid = t11.genreid AND t3.metaid = t2.metaid AND
+ t3.formatid = t4.formatid AND t4.formatclassid = t5.formatclassid AND
+ t4.canaddtocapability = 'Y' AND t5.formattypeid = t6.formattypeid AND
+ t6.formattypeid IN (2) AND (t3.formatid IN (31, 8, 76)) AND
+ t1.metaid = t2.metaid AND t1.affiliateid = '2';
+
set join_cache_level=6;
EXPLAIN
=== modified file 'sql/lex.h'
--- a/sql/lex.h 2008-08-16 05:15:49 +0000
+++ b/sql/lex.h 2008-09-22 12:29:33 +0000
@@ -275,6 +275,7 @@ static SYMBOL symbols[] = {
{ "ITERATE", SYM(ITERATE_SYM)},
{ "INVOKER", SYM(INVOKER_SYM)},
{ "JOIN", SYM(JOIN_SYM)},
+ { "JOIN_BUFFER", SYM(JOIN_BUFFER_SYM)},
{ "KEY", SYM(KEY_SYM)},
{ "KEYS", SYM(KEYS)},
{ "KEY_BLOCK_SIZE", SYM(KEY_BLOCK_SIZE)},
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h 2008-09-04 18:18:11 +0000
+++ b/sql/sql_lex.h 2008-09-22 12:29:33 +0000
@@ -820,12 +820,48 @@ public:
void clear_index_hints(void) { index_hints= NULL; }
+ void set_join_buffer_hint_size(ulong size)
+ {
+ current_join_buffer_hint_size= max(size, 128 + MALLOC_OVERHEAD);
+ current_join_buffer_hint_type |= JOIN_BUFFER_HINT_SIZE;
+ }
+
+ ulong get_join_buffer_hint_size(void)
+ {
+ return current_join_buffer_hint_size;
+ }
+
+ void set_join_buffer_hint_type(enum join_buffer_hint_type type)
+ {
+ current_join_buffer_hint_type= type;
+ }
+
+ int get_join_buffer_hint_type(void)
+ {
+ return current_join_buffer_hint_type;
+ }
+
+ void set_join_buffer_hint_level(int level)
+ {
+ current_join_buffer_hint_level= level;
+ current_join_buffer_hint_type |= JOIN_BUFFER_HINT_LEVEL;
+ }
+
+ int get_join_buffer_hint_level(void)
+ {
+ return current_join_buffer_hint_level;
+ }
+
private:
/* current index hint kind. used in filling up index_hints */
enum index_hint_type current_index_hint_type;
index_clause_map current_index_hint_clause;
/* a list of USE/FORCE/IGNORE INDEX */
List<Index_hint> *index_hints;
+
+ ulong current_join_buffer_hint_size; // ulong, like the global
+ int current_join_buffer_hint_type;
+ int current_join_buffer_hint_level;
};
typedef class st_select_lex SELECT_LEX;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-09-04 18:41:27 +0000
+++ b/sql/sql_select.cc 2008-09-22 12:29:33 +0000
@@ -6025,10 +6025,13 @@ best_access_path(JOIN *join,
}
else
{
- /* We read the table as many times as join buffer becomes full. */
+ /*
+ We read the table as many times as join buffer becomes full.
+ Heed hint for join-buffer size or, missing that, use sysvar.
+ */
tmp*= (1.0 + floor((double) cache_record_length(join,idx) *
record_count /
- (double) thd->variables.join_buff_size));
+ (double) s->get_buffer_size(thd)));
/*
We don't make full cartesian product between rows in the scanned
table and existing records because we skip all rows from the
@@ -8361,16 +8364,31 @@ void revise_cache_usage(JOIN_TAB *join_t
}
+/**
+ Should we use join-buffering at given level for given table?
+
+ @param tab The table to make the decision for
+ @param join Join being processed
+ @param options Join's options (checking for SELECT_DESCRIBE,
+ SELECT_NO_JOIN_CACHE)
+ @param no_jbuf_after Don't use join buffering after this table
+ @param cache_level The desired cache-level
+ @return TRUE if can use join-buffer at that level
+
+ @details
+ Check whether we can use join-buffering at the given level.
+ Called from check_join_cache_usage().
+ */
+
static
-bool check_join_cache_usage(JOIN_TAB *tab,
- JOIN *join, ulonglong options,
- uint no_jbuf_after)
+bool check_join_cache_at_level(JOIN_TAB *tab,
+ JOIN *join, ulonglong options,
+ uint no_jbuf_after, int cache_level)
{
uint flags;
COST_VECT cost;
uint bufsz= 4096;
JOIN_CACHE *prev_cache=0;
- uint cache_level= join->thd->variables.join_cache_level;
bool force_unlinked_cache= test(cache_level & 1);
uint i= tab-join->join_tab;
ha_rows rows;
@@ -8441,6 +8459,74 @@ no_join_cache:
return FALSE;
}
+
+/**
+ Find most appropriate cache-level for joining given table.
+
+ @param tab The table to make the decision for
+ @param join Join being processed
+ @param options Join's options (checking for SELECT_DESCRIBE,
+ SELECT_NO_JOIN_CACHE)
+ @param no_jbuf_after Don't use join buffering after this table
+ @return TRUE if we are to use join-buffer
+
+ @details
+ Apply all join-buffer hints except size-hints.
+ Then check whether we can use join-buffering at all, starting at the
+ level selected by the user and descending until we get a positive.
+ Called from make_join_readinfo().
+ */
+
+static
+bool check_join_cache_usage(JOIN_TAB *tab,
+ JOIN *join, ulonglong options,
+ uint no_jbuf_after)
+{
+ uint cache_level;
+ uint i= tab-join->join_tab;
+ uint hint= tab->table->pos_in_table_list->join_buffer_hint_type;
+
+ if (hint)
+ {
+ /*
+ join_buffer_hint_type can be one of FORCE/USE/IGNORE.
+ Since FORCE and USE are functionally equivalent for the time
+ being, we just need to bail here on IGNORE.
+ */
+ if (hint & JOIN_BUFFER_HINT_IGNORE)
+ return FALSE;
+
+ /*
+ If the user gave a level-hint, we'll start with that; otherwise,
+ we'll start with the maximum.
+ */
+ if (hint & JOIN_BUFFER_HINT_LEVEL)
+ cache_level= tab->table->pos_in_table_list->join_buffer_hint_level;
+ else
+ cache_level= 8;
+ }
+ else
+ /*
+ No hints given, use sysvar join-cache level.
+ */
+ cache_level= join->thd->variables.join_cache_level;
+
+ while (cache_level > 0)
+ {
+ if (check_join_cache_at_level(tab, join, options,
+ no_jbuf_after, cache_level))
+ {
+ if (tab->cache) // NULL for SELECT_DESCRIBE
+ tab->cache->cache_level= cache_level;
+ return TRUE;
+ }
+ cache_level--;
+ }
+
+ return FALSE;
+}
+
+
/*
Plan refinement stage: do various set ups for the executioner
@@ -17303,7 +17389,9 @@ void JOIN_CACHE::set_constants()
uint len= length + fields*sizeof(uint)+blobs*sizeof(uchar *) +
(prev_cache ? prev_cache->get_size_of_rec_offset() : 0) +
sizeof(ulong);
- buff_size= max(join->thd->variables.join_buff_size, 2*len);
+
+ buff_size= max(join_tab->get_buffer_size(join->thd), 2*len);
+
size_of_rec_ofs= offset_size(buff_size);
size_of_rec_len= blobs ? size_of_rec_ofs : offset_size(len);
size_of_fld_ofs= size_of_rec_len;
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2008-09-04 18:18:11 +0000
+++ b/sql/sql_select.h 2008-09-22 12:29:33 +0000
@@ -329,6 +329,16 @@ typedef struct st_join_table {
return first_inner;
return first_sj_inner_tab;
}
+ /*
+ If a size-hint was given, return that, otherwise use the sysvar.
+ */
+ ulong get_buffer_size(THD *thd)
+ {
+ return (table->pos_in_table_list->join_buffer_hint_type &
+ JOIN_BUFFER_HINT_SIZE)
+ ?table->pos_in_table_list->join_buffer_hint_size
+ :thd->variables.join_buff_size;
+ }
} JOIN_TAB;
/*
@@ -661,6 +671,9 @@ public:
/* Table to be joined with the partial join records from the cache */
JOIN_TAB *join_tab;
+ /* This identifies the level we got rather than the one we asked for. */
+ int cache_level;
+
/* Pointer to the previous join cache if there is any */
JOIN_CACHE *prev_cache;
/* Pointer to the next join cache if there is any */
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2008-09-04 18:18:11 +0000
+++ b/sql/sql_yacc.yy 2008-09-22 12:29:33 +0000
@@ -1194,6 +1194,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
%token YEAR_SYM /* SQL-2003-R */
%token ZEROFILL
%token BACKUP_TEST_SYM
+%token JOIN_BUFFER_SYM /* WL4421 */
%left JOIN_SYM INNER_SYM STRAIGHT_JOIN CROSS LEFT RIGHT
/* A dummy token to force the priority of table_ref production in a join. */
@@ -8775,6 +8776,7 @@ table_factor:
{
SELECT_LEX *sel= Select;
sel->table_join_options= 0;
+ sel->set_join_buffer_hint_type(JOIN_BUFFER_HINT_DEFAULT);
}
table_ident opt_table_alias opt_key_definition
{
@@ -8783,6 +8785,9 @@ table_factor:
Lex->lock_option,
Select->pop_index_hints())))
MYSQL_YYABORT;
+ $$->join_buffer_hint_size = Select->get_join_buffer_hint_size();
+ $$->join_buffer_hint_level= Select->get_join_buffer_hint_level();
+ $$->join_buffer_hint_type = Select->get_join_buffer_hint_type();
Select->add_joined_table($$);
}
| select_derived_init get_select_lex select_derived2
@@ -9006,6 +9011,32 @@ opt_outer:
| OUTER {}
;
+join_buffer_hint_size:
+ size_number
+ { Select->set_join_buffer_hint_size($1); }
+ ;
+
+join_buffer_hint_options:
+ join_buffer_hint_size
+ | join_buffer_hint_size ',' ulong_num
+ { Select->set_join_buffer_hint_level($3); }
+ | DEFAULT ',' ulong_num
+ { Select->set_join_buffer_hint_level($3); }
+ | DEFAULT
+ ;
+
+join_buffer_hint_type:
+ FORCE_SYM { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_FORCE); }
+ | USE_SYM { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_USE); }
+ ;
+
+join_buffer_hint_definition:
+ join_buffer_hint_type JOIN_BUFFER_SYM
+ | join_buffer_hint_type JOIN_BUFFER_SYM '(' join_buffer_hint_options ')'
+ | IGNORE_SYM JOIN_BUFFER_SYM
+ { Select->set_join_buffer_hint_type(JOIN_BUFFER_HINT_IGNORE); }
+ ;
+
index_hint_clause:
/* empty */
{
@@ -9023,7 +9054,8 @@ index_hint_type:
;
index_hint_definition:
- index_hint_type key_or_index index_hint_clause
+ join_buffer_hint_definition
+ | index_hint_type key_or_index index_hint_clause
{
Select->set_index_hint_type($1, $3);
}
=== modified file 'sql/table.h'
--- a/sql/table.h 2008-08-18 05:43:50 +0000
+++ b/sql/table.h 2008-09-22 12:29:33 +0000
@@ -563,6 +563,16 @@ enum index_hint_type
INDEX_HINT_FORCE
};
+enum join_buffer_hint_type
+{
+ JOIN_BUFFER_HINT_DEFAULT=0,
+ JOIN_BUFFER_HINT_IGNORE=1,
+ JOIN_BUFFER_HINT_USE=2,
+ JOIN_BUFFER_HINT_FORCE=4,
+ JOIN_BUFFER_HINT_SIZE=32,
+ JOIN_BUFFER_HINT_LEVEL=64
+};
+
struct st_table {
st_table() {} /* Remove gcc warning */
@@ -1126,6 +1136,12 @@ struct TABLE_LIST
List<Index_hint> *index_hints;
TABLE *table; /* opened table */
uint table_id; /* table id (from binlog) for opened table */
+
+ /* JOIN_BUFFER hints (USE/IGNORE/FORCE, SIZE) */
+ ulong join_buffer_hint_size;
+ int join_buffer_hint_level;
+ int join_buffer_hint_type;
+
/*
select_result for derived table to pass it from table creation to table
filling procedure
| Thread |
|---|
| • bzr commit into mysql-6.0-bka-preview branch (azundris:2648) WL#4421 | Tatiana A. Nurnberg | 22 Sep |
