#At file:///home/tnurnberg/forest/WL4421/
2637 Tatiana A. Nurnberg 2008-06-27
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
add tests for the JOIN_BUFFER hints syntax.
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
add tests for the JOIN_BUFFER hints syntax.
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-02-17 13:54:05 +0000
+++ b/mysql-test/r/group_by.result 2008-06-27 13:55:21 +0000
@@ -1478,7 +1478,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-06-06 22:03:14 +0000
+++ b/mysql-test/r/join_cache.result 2008-06-27 13:55:21 +0000
@@ -796,6 +796,387 @@ 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
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(256)
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+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 CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer
+1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(256)
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name Name Language
+Leiden Netherlands Dutch
+La Matanza Argentina Spanish
+Lomas de Zamora Argentina Spanish
+La Plata Argentina Spanish
+Lanús Argentina Spanish
+Las Heras Argentina Spanish
+La Rioja Argentina Spanish
+Liège Belgium Dutch
+La Paz Bolivia Spanish
+Londrina Brazil Portuguese
+Limeira Brazil Portuguese
+Lages Brazil Portuguese
+Luziânia Brazil Portuguese
+Lauro de Freitas Brazil Portuguese
+Linhares Brazil Portuguese
+London United Kingdom English
+Liverpool United Kingdom English
+Leeds United Kingdom English
+Leicester United Kingdom English
+Luton United Kingdom English
+Los Angeles Chile Spanish
+La Serena Chile Spanish
+La Romana Dominican Republic Spanish
+Loja Ecuador Spanish
+Luxor Egypt Arabic
+Las Palmas de Gran Canaria Spain Spanish
+L´Hospitalet de Llobregat Spain Spanish
+Leganés Spain Spanish
+León Spain Spanish
+Logroño Spain Spanish
+Lleida (Lole
+La Ceiba Honduras Spanish
+Livorno Italy Italian
+Latina Italy Italian
+Lecce Italy Italian
+La Spezia Italy Italian
+Linz Austria German
+London Canada English
+Laval Canada English
+Longueuil Canada English
+Lanzhou China Chinese
+Luoyang China Chinese
+Liuzhou China Chinese
+Liaoyang China Chinese
+Liupanshui China Chinese
+Liaoyuan China Chinese
+Lianyungang China Chinese
+Leshan China Chinese
+Linyi China Chinese
+Luzhou China Chinese
+Laiwu China Chinese
+Liaocheng China Chinese
+Laizhou China Chinese
+Linfen China Chinese
+Liangcheng China Chinese
+Longkou China Chinese
+Langfang China Chinese
+Liu´an China Chinese
+Longjing China Chinese
+Lengshuijiang China Chinese
+Laiyang China Chinese
+Longyan China Chinese
+Linhe China Chinese
+Leiyang China Chinese
+Loudi China Chinese
+Luohe China Chinese
+Linqing China Chinese
+Laohekou China Chinese
+Linchuan China Chinese
+Lhasa China Chinese
+Lianyuan China Chinese
+Liyang China Chinese
+Liling China Chinese
+Linhai China Chinese
+Larisa Greece Greek
+La Habana Cuba Spanish
+Lilongwe Malawi Chichewa
+León Mexico Spanish
+La Paz Mexico Spanish
+La Paz Mexico Spanish
+Lázaro Cárdenas Mexico Spanish
+Lagos de Moreno Mexico Spanish
+Lerdo Mexico Spanish
+Los Cabos Mexico Spanish
+Lerma Mexico Spanish
+Las Margaritas Mexico Spanish
+Lashio (Lasho) Myanmar Burmese
+Lalitapur Nepal Nepali
+León Nicaragua Spanish
+Lambaré Paraguay Spanish
+Lima Peru Spanish
+Lisboa Portugal Portuguese
+Lódz Poland Polish
+Lublin Poland Polish
+Legnica Poland Polish
+Lyon France French
+Le Havre France French
+Lille France French
+Le Mans France French
+Limoges France French
+Linköping Sweden Swedish
+Lund Sweden Swedish
+Leipzig Germany German
+Lübeck Germany German
+Ludwigshafen am Rhein Germany German
+Leverkusen Germany German
+Lünen Germany German
+Lahti Finland Finnish
+Lausanne Switzerland German
+Latakia Syria Arabic
+Luchou Taiwan Min
+Lungtan Taiwan Min
+Liberec Czech Republic Czech
+Lviv Ukraine Ukrainian
+Lugansk Ukraine Ukrainian
+Lutsk Ukraine Ukrainian
+Lysyt?ansk Ukraine Ukrainian
+Lower Hutt New Zealand English
+Lida Belarus Belorussian
+Los Teques Venezuela Spanish
+Lipetsk Russian Federation Russian
+Ljubertsy Russian Federation Russian
+Leninsk-Kuznetski Russian Federation Russian
+Long Xuyen Vietnam Vietnamese
+Los Angeles United States English
+Las Vegas United States English
+Long Beach United States English
+Lexington-Fayette United States English
+Louisville United States English
+Lincoln United States English
+Lubbock United States English
+Little Rock United States English
+Laredo United States English
+Lakewood United States English
+Lansing United States English
+Lancaster United States English
+Lafayette United States English
+Lowell United States English
+Livonia United States English
+JOIN_BUFFER(DEFAULT,2) must give the same results as join_cache_level=2
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(DEFAULT,2)
+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(DEFAULT,2)
+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
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(DEFAULT,2)
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+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 CountryLanguage ALL NULL NULL NULL NULL 984 Using where; Using join buffer
+1 SIMPLE City ALL NULL NULL NULL NULL 4079 Using where; Using join buffer
+SELECT City.Name, Country.Name, CountryLanguage.Language
+FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(DEFAULT,2)
+WHERE City.Country=Country.Code AND
+CountryLanguage.Country=Country.Code AND
+City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+CountryLanguage.Percentage > 50;
+Name Name Language
+Leiden Netherlands Dutch
+La Matanza Argentina Spanish
+Lomas de Zamora Argentina Spanish
+La Plata Argentina Spanish
+Lanús Argentina Spanish
+Las Heras Argentina Spanish
+La Rioja Argentina Spanish
+Liège Belgium Dutch
+La Paz Bolivia Spanish
+Londrina Brazil Portuguese
+Limeira Brazil Portuguese
+Lages Brazil Portuguese
+Luziânia Brazil Portuguese
+Lauro de Freitas Brazil Portuguese
+Linhares Brazil Portuguese
+London United Kingdom English
+Liverpool United Kingdom English
+Leeds United Kingdom English
+Leicester United Kingdom English
+Luton United Kingdom English
+Los Angeles Chile Spanish
+La Serena Chile Spanish
+La Romana Dominican Republic Spanish
+Loja Ecuador Spanish
+Luxor Egypt Arabic
+Las Palmas de Gran Canaria Spain Spanish
+L´Hospitalet de Llobregat Spain Spanish
+Leganés Spain Spanish
+León Spain Spanish
+Logroño Spain Spanish
+Lleida (Lérida) Spain Spanish
+Le-Cap-Haïtien Haiti Haiti Creole
+La Ceiba Honduras Spanish
+Livorno Italy Italian
+Latina Italy Italian
+Lecce Italy Italian
+La Spezia Italy Italian
+Linz Austria German
+London Canada English
+Laval Canada English
+Longueuil Canada English
+Lanzhou China Chinese
+Luoyang China Chinese
+Liuzhou China Chinese
+Liaoyang China Chinese
+Liupanshui China Chinese
+Liaoyuan China Chinese
+Lianyungang China Chinese
+Leshan China Chinese
+Linyi China Chinese
+Luzhou China Chinese
+Laiwu China Chinese
+Liaocheng China Chinese
+Laizhou China Chinese
+Linfen China Chinese
+Liangcheng China Chinese
+Longkou China Chinese
+Langfang China Chinese
+Liu´an China Chinese
+Longjing China Chinese
+Lengshuijiang China Chinese
+Laiyang China Chinese
+Longyan China Chinese
+Linhe China Chinese
+Leiyang China Chinese
+Loudi China Chinese
+Luohe China Chinese
+Linqing China Chinese
+Laohekou China Chinese
+Linchuan China Chinese
+Lhasa China Chinese
+Lianyuan China Chinese
+Liyang China Chinese
+Liling China Chinese
+Linhai China Chinese
+Larisa Greece Greek
+La Habana Cuba Spanish
+Lilongwe Malawi Chichewa
+León Mexico Spanish
+La Paz Mexico Spanish
+La Paz Mexico Spanish
+Lázaro Cárdenas Mexico Spanish
+Lagos de Moreno Mexico Spanish
+Lerdo Mexico Spanish
+Los Cabos Mexico Spanish
+Lerma Mexico Spanish
+Las Margaritas Mexico Spanish
+Lashio (Lasho) Myanmar Burmese
+Lalitapur Nepal Nepali
+León Nicaragua Spanish
+Lambaré Paraguay Spanish
+Lima Peru Spanish
+Lisboa Portugal Portuguese
+Lódz Poland Polish
+Lublin Poland Polish
+Legnica Poland Polish
+Lyon France French
+Le Havre France French
+Lille France French
+Le Mans France French
+Limoges France French
+Linköping Sweden Swedish
+Lund Sweden Swedish
+Leipzig Germany German
+Lübeck Germany German
+Ludwigshafen am Rhein Germany German
+Leverkusen Germany German
+Lünen Germany German
+Lahti Finland Finnish
+Lausanne Switzerland German
+Latakia Syria Arabic
+Luchou Taiwan Min
+Lungtan Taiwan Min
+Liberec Czech Republic Czech
+Lviv Ukraine Ukrainian
+Lugansk Ukraine Ukrainian
+Lutsk Ukraine Ukrainian
+Lysyt?ansk Ukraine Ukrainian
+Lower Hutt New Zealand English
+Lida Belarus Belorussian
+Los Teques Venezuela Spanish
+Lipetsk Russian Federation Russian
+Ljubertsy Russian Federation Russian
+Leninsk-Kuznetski Russian Federation Russian
+Long Xuyen Vietnam Vietnamese
+Los Angeles United States English
+Las Vegas United States English
+Long Beach United States English
+Lexington-Fayette United States English
+Louisville United States English
+Lincoln United States English
+Lubbock United States English
+Little Rock United States English
+Laredo United States English
+Lakewood United States English
+Lansing United States English
+Lancaster United States English
+Lafayette United States English
+Lowell United States English
+Livonia United States English
+show that empty or rubbish hints are not tolerated
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER('BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER() WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: Only integers allowed as number here near '4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a' at line 1
DROP DATABASE world;
CREATE DATABASE world;
use world;
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2008-02-17 13:54:05 +0000
+++ b/mysql-test/t/group_by.test 2008-06-27 13:55:21 +0000
@@ -945,7 +945,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-06-03 01:49:09 +0000
+++ b/mysql-test/t/join_cache.test 2008-06-27 13:55:21 +0000
@@ -140,6 +140,76 @@ 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;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(256)
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(256)
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+--echo JOIN_BUFFER(DEFAULT,2) must give the same results as join_cache_level=2
+
+EXPLAIN
+SELECT City.Name, Country.Name FROM City,Country FORCE JOIN_BUFFER(DEFAULT,2)
+ 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(DEFAULT,2)
+ WHERE City.Country=Country.Code AND
+ Country.Name LIKE 'L%' AND City.Population > 100000;
+
+EXPLAIN
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(DEFAULT,2)
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+SELECT City.Name, Country.Name, CountryLanguage.Language
+ FROM City,Country,CountryLanguage FORCE JOIN_BUFFER(DEFAULT,2)
+ WHERE City.Country=Country.Code AND
+ CountryLanguage.Country=Country.Code AND
+ City.Name LIKE 'L%' AND Country.Population > 3000000 AND
+ CountryLanguage.Percentage > 50;
+
+--echo show that empty or rubbish hints are not tolerated
+
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER('BigFunkyCat') WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER() WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(4.5) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 FORCE JOIN_BUFFER(-1) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+--error ER_PARSE_ERROR
+SELECT * FROM t1, t2, t3 IGNORE JOIN_BUFFER(1024) WHERE t1.a=t2.a AND t2.b=t3.b AND t1.d < 5 ORDER BY t1.a,t3.a;
+
+
DROP DATABASE world;
=== modified file 'sql/lex.h'
--- a/sql/lex.h 2008-02-22 13:58:52 +0000
+++ b/sql/lex.h 2008-06-27 13:55:21 +0000
@@ -273,6 +273,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-04-02 12:04:51 +0000
+++ b/sql/sql_lex.h 2008-06-27 13:55:21 +0000
@@ -811,12 +811,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-06-12 04:27:24 +0000
+++ b/sql/sql_select.cc 2008-06-27 13:55:21 +0000
@@ -5871,10 +5871,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
@@ -8202,19 +8205,34 @@ 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;
-
+
if (cache_level == 0)
return FALSE;
if (i == join->const_tables)
@@ -8281,6 +8299,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
@@ -16911,7 +16997,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;
@@ -18939,7 +19027,7 @@ int JOIN_CACHE_BKA_UNIQUE::init()
/* Calculate the minimal possible value of size_of_key_ofs greater than 1 */
for (size_of_key_ofs= 2;
- size_of_key_ofs <= get_size_of_rec_offset();
+ size_of_key_ofs <= max(2, get_size_of_rec_offset());
size_of_key_ofs+= 2)
{
key_entry_length= get_size_of_rec_offset() + // key chain header
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2008-06-11 15:57:58 +0000
+++ b/sql/sql_select.h 2008-06-27 13:55:21 +0000
@@ -325,6 +325,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;
/*
@@ -657,6 +667,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-04-02 12:04:51 +0000
+++ b/sql/sql_yacc.yy 2008-06-27 13:55:21 +0000
@@ -1188,6 +1188,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. */
@@ -8090,6 +8091,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
{
@@ -8098,6 +8100,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
@@ -8321,6 +8326,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 */
{
@@ -8338,7 +8369,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-04-02 12:04:51 +0000
+++ b/sql/table.h 2008-06-27 13:55:21 +0000
@@ -452,6 +452,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 */
@@ -1003,6 +1013,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 branch (azundris:2637) WL#4421 | Tatiana A. Nurnberg | 27 Jun |
