diff options
| -rw-r--r-- | BitKeeper/etc/logging_ok | 26 | ||||
| -rw-r--r-- | Docs/manual.texi | 178 | ||||
| -rw-r--r-- | extra/my_print_defaults.c | 14 | ||||
| -rw-r--r-- | include/my_sys.h | 1 | ||||
| -rw-r--r-- | mysys/default.c | 48 | ||||
| -rw-r--r-- | sql/sql_acl.cc | 10 | ||||
| -rw-r--r-- | sql/sql_select.cc | 89 | ||||
| -rw-r--r-- | sql/sql_select.h | 36 |
8 files changed, 277 insertions, 125 deletions
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok index 5acbc173ec6..a3e121b77e3 100644 --- a/BitKeeper/etc/logging_ok +++ b/BitKeeper/etc/logging_ok @@ -1,25 +1 @@ -bk@work.mysql.com -davida@work.mysql.com -jamppa@work.mysql.com -jcole@ham.spaceapes.com -jcole@jcole.burghcom.com -jcole@nslinux.bedford.progress.com -jcole@tetra.bedford.progress.com -jcole@tetra.spaceapes.com -matt@work.mysql.com -monty@donna.mysql.com -monty@work.mysql.com -mwagner@evoq.home.mwagner.org -nusphere@main.burghcom.com -paul@central.snake.net -paul@work.mysql.com -sasha@mysql.sashanet.com -sasha@work.mysql.com -serg@infomag.ape.relarn.ru -serg@work.mysql.com -sinisa@work.mysql.com -spurr@nslinux.bedford.progress.com -tim@localhost.polyesthetic.msg -tim@threads.polyesthetic.msg -tim@work.mysql.com -tonu@work.mysql.com +monty@tramp.mysql.fi diff --git a/Docs/manual.texi b/Docs/manual.texi index 133cb095419..4c056d1ff90 100644 --- a/Docs/manual.texi +++ b/Docs/manual.texi @@ -305,14 +305,14 @@ Post-installation Setup and Testing * Command-line options:: Command-line options * Option files:: Option files -Is there anything special to do when upgrading/downgrading MySQL? +Is There Anything Special to Do when Upgrading/Downgrading MySQL? * Upgrading-from-3.22:: Upgrading from a 3.22 version to 3.23 * Upgrading-from-3.21:: Upgrading from a 3.21 version to 3.22 * Upgrading-from-3.20:: Upgrading from a 3.20 version to 3.21 * Upgrading-to-arch:: Upgrading to another architecture -How standards-compatible is MySQL? +How Standards-compatible Is MySQL? * Extensions to ANSI:: @strong{MySQL} extensions to ANSI SQL92 * Ansi mode:: Running @strong{MySQL} in ANSI mode @@ -321,7 +321,7 @@ How standards-compatible is MySQL? * Standards:: What standards does @strong{MySQL} follow? * Commit-rollback:: How to cope without @code{COMMIT}-@code{ROLLBACK} -Functionality missing from MySQL +Functionality Missing from MySQL * Missing Sub-selects:: Sub-selects * Missing SELECT INTO TABLE:: @code{SELECT INTO TABLE} @@ -335,7 +335,7 @@ Foreign Keys * Broken Foreign KEY:: Reasons NOT to use foreign keys -The MySQL access privilege system +The MySQL Access Privilege System * General security:: General security * Security:: How to make @strong{MySQL} secure against crackers @@ -353,7 +353,7 @@ The MySQL access privilege system * Passwords:: How to set up passwords * Access denied:: Causes of @code{Access denied} errors -MySQL language reference +MySQL Language Reference * Literals:: Literals: how to write strings and numbers * Variables:: User variables @@ -394,7 +394,7 @@ MySQL language reference * CREATE FUNCTION:: @code{CREATE FUNCTION} syntax * Reserved words:: Is @strong{MySQL} picky about reserved words? -Literals: how to write strings and numbers +Literals: How to Write Strings and Numbers * String syntax:: Strings * Number syntax:: Numbers @@ -402,11 +402,11 @@ Literals: how to write strings and numbers * NULL values:: @code{NULL} values * Legal names:: Database, table, index, column and alias names -Database, table, index, column and alias names +Database, Table, Index, Column, and Alias Names * Name case sensitivity:: Case sensitivity in names -Column types +Column Types * Storage requirements:: Column type storage requirements * Numeric types:: Numeric types @@ -417,21 +417,21 @@ Column types * Multiple-column indexes:: Multiple-column indexes * Other-vendor column types:: Using column types from other database engines -Date and time types +Date and Time Types * Y2K issues:: Y2K issues and date types * DATETIME:: The @code{DATETIME}, @code{DATE} and @code{TIMESTAMP} types * TIME:: The @code{TIME} type * YEAR:: The @code{YEAR} type -String types +String Types * CHAR:: The @code{CHAR} and @code{VARCHAR} types * BLOB:: The @code{BLOB} and @code{TEXT} types * ENUM:: The @code{ENUM} type * SET:: The @code{SET} type -Functions for use in @code{SELECT} and @code{WHERE} clauses +Functions for Use in @code{SELECT} and @code{WHERE} Clauses * Grouping functions:: Grouping functions * Arithmetic functions:: Normal arithmetic operations @@ -447,7 +447,7 @@ Functions for use in @code{SELECT} and @code{WHERE} clauses * Miscellaneous functions:: Miscellaneous functions * Group by functions:: Functions for @code{GROUP BY} clause -@code{CREATE TABLE} syntax +@code{CREATE TABLE} Syntax * Silent column changes:: Silent column changes @@ -580,6 +580,7 @@ Speed of queries that access or update data * Estimating performance:: Estimating query performance * SELECT speed:: Speed of @code{SELECT} queries * Where optimizations:: How MySQL optimizes @code{WHERE} clauses +* DISTINCT optimization:: * LEFT JOIN optimization:: How MySQL optimizes @code{LEFT JOIN} * LIMIT optimization:: How MySQL optimizes @code{LIMIT} * Insert speed:: Speed of @code{INSERT} queries @@ -4393,14 +4394,47 @@ last version number is newer than yours). We have tried to fix only fatal bugs and make small, relatively safe changes to that version. @end itemize -The second decision to make is whether you want to use a source distribution or -a binary distribution: +The second decision to make is whether you want to use a source +distribution or a binary distribution. In most cases you should probably +use a binary distribution, if there exist one for your platform, as this +is generally, it will be easier to install than a source distribution. + +In the following cases you will probably be better of with a source +installation: + +@itemize @bullet +@item +If you want to install @strong{MySQL} at some explicit location. (The standard +binary distributions are 'ready to run' at any place, but you may want +to get even more flexibility). + +@item +If you want to configure @code{mysqld} with some extra feature that is NOT in +the standard binary distributions. Here follows a list of the most common +extra options that you may want to use @itemize @bullet +@item --with-berkeley-db +@item --with-raid +@item --with-libwrap +@item --with-named-z-lib (This is done for some of the binaries) +@item --with-debugging[=full] +@end itemize + +@item +The default binary distribution is normally compiled with support +for all characters sets and should work on a variety of processors from +the same processor family. + +If you want a faster @strong{MySQL} server you may want to recompile it +with support for only the character sets you need, use a better compiler +(like pgcc) or use compiler options that are better optimized for your +processor. + @item -If you want to run @strong{MySQL} on a platform for which a current binary -distribution exists, use that. Generally, it will be easier to install -than a source distribution. +If you have found a bug and reported it to the @strong{MySQL} +development team you will probably got a patch that you need to apply to +the source distribution to get the bug fixed. @item If you want to read (and/or modify) the C and C++ code that makes up @@ -9122,6 +9156,7 @@ for clients from option files. @item @strong{Filename} @tab @strong{Purpose} @item @code{/etc/my.cnf} @tab Global options @item @code{DATADIR/my.cnf} @tab Server-specific options +@item @code{defaults-extra-file} @tab The file specified with --defaults-extra-file=# @item @code{~/.my.cnf} @tab User-specific options @end multitable @@ -9233,15 +9268,19 @@ there are sample configuration files for small, medium, large, and very large systems. You can copy @file{my-xxxx.cnf} to your home directory (rename the copy to @file{.my.cnf}) to experiment with this. -To tell a @strong{MySQL} program not to read any option files, specify -@code{--no-defaults} as the first option on the command line. This -@strong{MUST} be the first option or it will have no effect! -If you want to check which options are used, you can give the option -@code{--print-defaults} as the first option. +All @strong{MySQL} clients that support option files support the +following options: -If you want to force the use of a specific config file, you can use the option -@code{--defaults-file=full-path-to-default-file}. If you do this, only the -specified file will be read. +@multitable @columnfractions .40 .60 +@item --no-defaults @tab Don't read any option files. +@item --print-defaults @tab Print the program name and all options that it will get. +@item --defaults-file=full-path-to-default-file @tab Only use the given configuration file. +@item --defaults-extra-file=full-path-to-default-file @tab Read this configuration file after the global configuration file but before the user configuration file. +@end multitable + +Note that the above options must be first on the command line to work! +@code{--print-defaults} may however be used directly after the +@code{--defaults-xxx-file} commands. Note for developers: Option file handling is implemented simply by processing all matching options (that is, options in the appropriate group) @@ -9252,6 +9291,19 @@ read option files, you need add only two lines to give it that capability. Check the source code of any of the standard @strong{MySQL} clients to see how to do this. +In shell scripts you can use the @file{my_print_defaults} command to parse the +config files: + +@example + +shell> my_print_defaults client mysql +--port=3306 +--socket=/tmp/mysql.sock +--no-auto-rehash +@end example + +The above output contains all options for the groups 'client' and 'mysql'. + @node Upgrade, , Post-installation, Installing @section Is There Anything Special to Do when Upgrading/Downgrading MySQL? @cindex upgrading @@ -20035,6 +20087,9 @@ resolve the query. Here follows an explanation of the different text strings that can be found in this column: @table @code +@item Distinct +@strong{MySQL} will not continue searching for more rows for the current row +combination after it has found the first matching row. @item Not exists @strong{MySQL} was able to do a @code{LEFT JOIN} optimization on the query and will not examine more rows in this table for a row combination @@ -20780,8 +20835,27 @@ The biggest differences between the ANSI SQL and @strong{MySQL} versions of @itemize @bullet @item +In @strong{MySQL} privileges are given for an username + hostname combination +and not only for an username. + +@item ANSI SQL doesn't have global or database-level privileges and ANSI SQL doesn't support all privilege types that @strong{MySQL} supports. +@strong{MySQL} doesn't support the ANSI SQL @code{TRIGGER}, @code{EXECUTE} or +@code{UNDER} privileges. + +@item +ANSI SQL privileges are structured in a hierarchal manner. If you remove +an user, all privileges the user has granted are revoked. In +@strong{MySQL} the granted privileges are not automaticly revoked, but +you have to revoke these yourself if needed. + +@item +If you in @code{MySQL} have the @code{INSERT} grant on only part of the +columns in a table, you can execute @code{INSERT} statements on the +table; The columns for which you don't have the @code{INSERT} privilege +will set to their default values. ANSI SQL requires you to have the +@code{INSERT} privilege on all columns. @item When you drop a table in ANSI SQL, all privileges for the table are revoked. @@ -26092,6 +26166,7 @@ great tool to find out if this is a problem with your query. * Estimating performance:: Estimating query performance * SELECT speed:: Speed of @code{SELECT} queries * Where optimizations:: How MySQL optimizes @code{WHERE} clauses +* DISTINCT optimization:: * LEFT JOIN optimization:: How MySQL optimizes @code{LEFT JOIN} * LIMIT optimization:: How MySQL optimizes @code{LIMIT} * Insert speed:: Speed of @code{INSERT} queries @@ -26171,7 +26246,7 @@ time for a large table! @cindex optimizations @findex WHERE -@node Where optimizations, LEFT JOIN optimization, SELECT speed, Query Speed +@node Where optimizations, DISTINCT optimization, SELECT speed, Query Speed @subsection How MySQL optimizes @code{WHERE} clauses The @code{WHERE} optimizations are put in the @code{SELECT} part here because @@ -26244,18 +26319,14 @@ possibilities. If all columns in @code{ORDER BY} and in @code{GROUP BY} come from the same table, then this table is preferred first when joining. @item -If there is an @code{ORDER BY} clause and a different @code{GROUP BY} clause, -or if the @code{ORDER BY} or @code{GROUP BY} -contains columns from tables other than the first table in the join -queue, a temporary table is created. +If there is an @code{ORDER BY} clause and a different @code{GROUP BY} +clause, or if the @code{ORDER BY} or @code{GROUP BY} contains columns +from tables other than the first table in the join queue, a temporary +table is created. @item If you use @code{SQL_SMALL_RESULT}, @strong{MySQL} will use an in-memory temporary table. @item -As @code{DISTINCT} is converted to a @code{GROUP BY} on all columns, -@code{DISTINCT} combined with @code{ORDER BY} will in many cases also need -a temporary table. -@item Each table index is queried and the best index that spans fewer than 30% of the rows is used. If no such index can be found, a quick table scan is used. @item @@ -26298,9 +26369,32 @@ mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,... @end example +@findex DISTINCT +@cindex optimizing, DISTINCT +@node DISTINCT optimization, LEFT JOIN optimization, Where optimizations, Query Speed +@subsection How MySQL optimizes @code{DISTINCT} + +@code{DISTINCT} is converted to a @code{GROUP BY} on all columns, +@code{DISTINCT} combined with @code{ORDER BY} will in many cases also +need a temporary table. + +When combining @code{LIMIT #} with @code{DISTINCT}, @strong{MySQL} will stop +as soon as it finds @code{#} unique rows. + +If you don't use columns from all used tables, @strong{MySQL} will stop +the scanning of the not used tables as soon as it has found the first match. + +@example +SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a; +@end example + +In the case, assuming t1 is used before t2 (check with @code{EXPLAIN}), then +@strong{MySQL} will stop reading from t2 (for that particular row in t1) +when the first row in t2 is found. + @findex LEFT JOIN @cindex optimizing, LEFT JOIN -@node LEFT JOIN optimization, LIMIT optimization, Where optimizations, Query Speed +@node LEFT JOIN optimization, LIMIT optimization, DISTINCT optimization, Query Speed @subsection How MySQL optimizes @code{LEFT JOIN} and @code{RIGHT JOIN} @code{A LEFT JOIN B} is in @strong{MySQL} implemented as follows: @@ -31397,6 +31491,10 @@ Load the privilege tables with: @code{mysqladmin -h hostname flush-privileges} or with the SQL command @code{FLUSH PRIVILEGES}. @end enumerate +Note that after you started @code{mysqld} with @code{--skip-grant-tables}, +any usage of @code{GRANT} commands will give you an @code{Unknown command} +error until you have executed @code{FLUSH PRIVILEGES}. + @cindex files, permissions @cindex error mesaages, can't find file @cindex files, not found message @@ -37760,6 +37858,16 @@ though, so 3.23 is not released as a stable version yet. @appendixsubsec Changes in release 3.23.26 @itemize @bullet @item +Added optimization of queries where @code{DISTINCT} is only used on columns +from some of the tables. +@item +Allow floating point numbers where there are no sign after the exponent +(like 1e1). +@item +@code{SHOW GRANTS} didn't always show all column grants. +@item +Added @code{--default-extra-file=#} to all @strong{MySQL} clients. +@item Automatic repair of @code{MyISAM} tables. @item Columns referenced in @code{INSERT} are are now properly initialized. diff --git a/extra/my_print_defaults.c b/extra/my_print_defaults.c index 01fc4108352..ad15793617b 100644 --- a/extra/my_print_defaults.c +++ b/extra/my_print_defaults.c @@ -32,6 +32,8 @@ static struct option long_options[] = { {"config-file", required_argument, 0, 'c'}, {"defaults-file", required_argument, 0, 'c'}, + {"defaults-extra-file", required_argument, 0, 'e'}, + {"extra-file", required_argument, 0, 'e'}, {"no-defaults", no_argument, 0, 'd'}, {"help", no_argument, 0, '?'}, {"version", no_argument, 0, 'V'}, @@ -40,7 +42,7 @@ static struct option long_options[] = static void usage(my_bool version) { - printf("%s Ver 1.1 for %s at %s\n",my_progname,SYSTEM_TYPE, + printf("%s Ver 1.2 for %s at %s\n",my_progname,SYSTEM_TYPE, MACHINE_TYPE); if (version) return; @@ -50,6 +52,9 @@ static void usage(my_bool version) printf("\n\ -c, --config-file=#, --defaults-file=#\n\ The config file to use (default '%s')\n\ + -e, --extra-file=#, --defaults-extra-file=#\n\ + Read this file after the global /etc config file and\n\ + before the config file in the users home directory.\n\ --no-defaults Return an empty string (useful for scripts)\n\ -?, --help Display this help message and exit.\n\ -V, --version Output version information and exit.\n", @@ -61,13 +66,16 @@ static int get_options(int *argc,char ***argv) { int c,option_index; - while ((c=getopt_long(*argc,*argv,"c:V?I", + while ((c=getopt_long(*argc,*argv,"c:e:V?I", long_options, &option_index)) != EOF) { switch (c) { case 'c': config_file=optarg; break; + case 'e': + defaults_extra_file=optarg; /* Used by the load_defaults */ + break; case 'n': exit(0); case 'I': @@ -102,7 +110,7 @@ int main(int argc, char **argv) if (get_options(&argc,&argv)) exit(1); if (!(load_default_groups=(char**) my_malloc((argc+2)*sizeof(char*), - MYF(MY_WME)))) + MYF(MY_WME)))) exit(1); for (count=0; *argv ; argv++,count++) diff --git a/include/my_sys.h b/include/my_sys.h index 77057ffa439..fd608599ba3 100644 --- a/include/my_sys.h +++ b/include/my_sys.h @@ -206,6 +206,7 @@ extern my_bool NEAR my_disable_locking,NEAR my_disable_async_io, NEAR my_disable_flush_key_blocks; extern char wild_many,wild_one,wild_prefix; extern const char *charsets_dir; +extern char *defaults_extra_file; typedef struct wild_file_pack /* Struct to hold info when selecting files */ { diff --git a/mysys/default.c b/mysys/default.c index 10b0d6c7f5c..dda96d5092a 100644 --- a/mysys/default.c +++ b/mysys/default.c @@ -29,8 +29,9 @@ ** The following arguments are handled automaticly; If used, they must be ** first argument on the command line! ** --no-defaults ; no options are read. -** --print-defaults ; Print the modified command line and exit ** --defaults-file=full-path-to-default-file ; Only this file will be read. +** --defaults-extra-file=full-path-to-default-file ; Read this file before ~/ +** --print-defaults ; Print the modified command line and exit ****************************************************************************/ #undef SAFEMALLOC /* safe_malloc is not yet initailized */ @@ -39,6 +40,8 @@ #include "m_string.h" #include "m_ctype.h" +char *defaults_extra_file=0; + /* Which directories are searched for options (and in which order) */ const char *default_directories[]= { @@ -50,6 +53,7 @@ const char *default_directories[]= { #ifdef DATADIR DATADIR, #endif +"", /* Place for defaults_extra_dir */ #ifndef __WIN__ "~/", #endif @@ -71,9 +75,10 @@ void load_defaults(const char *conf_file, const char **groups, int *argc, char ***argv) { DYNAMIC_ARRAY args; - const char **dirs, *extra_default_file; + const char **dirs, *forced_default_file; TYPELIB group; my_bool found_print_defaults=0; + uint args_used=0; MEM_ROOT alloc; char *ptr,**res; DBUG_ENTER("load_defaults"); @@ -97,9 +102,20 @@ void load_defaults(const char *conf_file, const char **groups, } /* Check if we want to force the use a specific default file */ - extra_default_file=0; - if (*argc >= 2 && is_prefix(argv[0][1],"--defaults-file=")) - extra_default_file=strchr(argv[0][1],'=')+1; + forced_default_file=0; + if (*argc >= 2) + { + if (is_prefix(argv[0][1],"--defaults-file=")) + { + forced_default_file=strchr(argv[0][1],'=')+1; + args_used++; + } + else if (is_prefix(argv[0][1],"--defaults-extra-file=")) + { + defaults_extra_file=strchr(argv[0][1],'=')+1; + args_used++; + } + } group.count=0; group.name= "defaults"; @@ -109,9 +125,9 @@ void load_defaults(const char *conf_file, const char **groups, if (init_dynamic_array(&args, sizeof(char*),*argc, 32)) goto err; - if (extra_default_file) + if (forced_default_file) { - if (search_default_file(&args, &alloc, "", extra_default_file, "", + if (search_default_file(&args, &alloc, "", forced_default_file, "", &group)) goto err; } @@ -132,8 +148,14 @@ void load_defaults(const char *conf_file, const char **groups, #endif for (dirs=default_directories ; *dirs; dirs++) { - if (search_default_file(&args, &alloc, *dirs, conf_file, default_ext, - &group)) + int error; + if (**dirs) + error=search_default_file(&args, &alloc, *dirs, conf_file, + default_ext, &group); + else if (defaults_extra_file) + error=search_default_file(&args, &alloc, NullS, defaults_extra_file, + default_ext, &group); + if (error) goto err; } } @@ -145,11 +167,9 @@ void load_defaults(const char *conf_file, const char **groups, /* copy name + found arguments + command line arguments to new array */ res[0]=argv[0][0]; memcpy((gptr) (res+1), args.buffer, args.elements*sizeof(char*)); - if (extra_default_file) - { - --*argc; /* Skipp --defaults-file */ - ++*argv; - } + /* Skipp --defaults-file and --defaults-extra-file */ + (*argc)-= args_used; + (*argv)+= args_used; /* Check if we wan't to see the new argument list */ if (*argc >= 2 && !strcmp(argv[0][1],"--print-defaults")) diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index f103fce1ad9..85d57914c78 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -2004,6 +2004,8 @@ bool check_grant(THD *thd, uint want_access, TABLE_LIST *tables, want_access &= ~table->grant.privilege; goto err; // No grants } + if (show_table) + continue; // We have some priv on this table->grant.grant_table=grant_table; // Remember for column test table->grant.version=grant_version; @@ -2013,8 +2015,6 @@ bool check_grant(THD *thd, uint want_access, TABLE_LIST *tables, if (!(~table->grant.privilege & want_access)) continue; - if (show_table && table->grant.privilege) - continue; // Test from show tables if (want_access & ~(grant_table->cols | table->grant.privilege)) { @@ -2457,18 +2457,18 @@ int mysql_show_grants(THD *thd,LEX_USER *lex_user) !strcmp(lex_user->host.str,host)) { want_access=grant_table->privs; - if (want_access) + if ((want_access | grant_table->cols) != 0) { String global(buff,sizeof(buff)); global.length(0); global.append("GRANT ",6); - if (test_all_bits(want_access,(TABLE_ACLS & ~GRANT_ACL))) + if (test_all_bits(grant_table->privs,(TABLE_ACLS & ~GRANT_ACL))) global.append("ALL PRIVILEGES",14); else { int found=0; - uint j,test_access= want_access & ~GRANT_ACL; + uint j,test_access= (want_access | grant_table->cols) & ~GRANT_ACL; for (counter=0, j = SELECT_ACL;j <= TABLE_ACLS; counter++,j <<= 1) { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 450efb84e5d..b7eebf80313 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -136,7 +136,8 @@ static void copy_sum_funcs(Item_sum **func_ptr); static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab); static void init_sum_functions(Item_sum **func); static bool update_sum_func(Item_sum **func); -static void select_describe(JOIN *join, bool need_tmp_table, bool need_order); +static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, + bool distinct); static void describe_info(const char *info); /***************************************************************************** @@ -171,6 +172,7 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds, no_order=0; bzero((char*) &keyuse,sizeof(keyuse)); thd->proc_info="init"; + thd->used_tables=0; // Updated by setup_fields if (setup_fields(thd,tables,fields,1,&all_fields) || setup_conds(thd,tables,&conds) || @@ -261,7 +263,7 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds, join.join_tab=0; join.tmp_table_param.copy_field=0; join.sum_funcs=0; - join.send_records=0L; + join.send_records=join.found_records=0; join.tmp_table_param.end_write_records= HA_POS_ERROR; join.first_record=join.sort_and_group=0; join.select_options=select_options; @@ -506,7 +508,8 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds, order=0; select_describe(&join,need_tmp, (order != 0 && - (!need_tmp || order != group || simple_group))); + (!need_tmp || order != group || simple_group)), + select_distinct); error=0; goto err; } @@ -557,6 +560,26 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds, order=0; } } + + /* + Optimize distinct when used on some of the tables + SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.b=t2.b + In this case we can stop scanning t2 when we have found one t1.a + */ + + if (tmp_table->distinct) + { + table_map used_tables= thd->used_tables; + JOIN_TAB *join_tab=join.join_tab+join.tables-1; + do + { + if (used_tables & join_tab->table->map) + break; + join_tab->not_used_in_distinct=1; + } while (join_tab-- != join.join_tab); + } + + /* Copy data to the temporary table */ thd->proc_info="Copying to tmp table"; if (do_select(&join,(List<Item> *) 0,tmp_table,0)) goto err; /* purecov: inspected */ @@ -2122,7 +2145,7 @@ make_simple_join(JOIN *join,TABLE *tmp_table) join->tmp_table_param.copy_field=0; join->first_record=join->sort_and_group=0; join->sum_funcs=0; - join->send_records=0L; + join->send_records=(ha_rows) 0; join->group=0; join_tab->cache.buff=0; /* No cacheing */ @@ -2130,15 +2153,16 @@ make_simple_join(JOIN *join,TABLE *tmp_table) join_tab->select=0; join_tab->select_cond=0; join_tab->quick=0; - bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); join_tab->type= JT_ALL; /* Map through all records */ join_tab->keys= (uint) ~0; /* test everything in quick */ join_tab->info=0; join_tab->on_expr=0; join_tab->ref.key = -1; + join_tab->not_used_in_distinct=0; + join_tab->read_first_record= join_init_read_record; + bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); tmp_table->status=0; tmp_table->null_row=0; - join_tab->read_first_record= join_init_read_record; return FALSE; } @@ -3256,7 +3280,6 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, if (item->with_sum_func && type != Item::SUM_FUNC_ITEM || item->const_item()) continue; - if (type == Item::SUM_FUNC_ITEM && !group && !save_sum_fields) { /* Can't calc group yet */ ((Item_sum*) item)->result_field=0; @@ -3913,7 +3936,9 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) if (!(error=(*join_tab->read_first_record)(join_tab))) { - bool not_exists_optimize=join_tab->table->reginfo.not_exists_optimize; + bool not_exists_optimize= join_tab->table->reginfo.not_exists_optimize; + bool not_used_in_distinct=join_tab->not_used_in_distinct; + ha_rows found_records=join->found_records; READ_RECORD *info= &join_tab->read_record; do @@ -3932,6 +3957,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) { if ((error=(*next_select)(join,join_tab+1,0)) < 0) return error; + if (not_used_in_distinct && found_records != join->found_records) + return 0; } } } while (!(error=info->read_record(info))); @@ -4545,23 +4572,21 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), } if (!join->having || join->having->val_int()) { + join->found_records++; if ((error=table->file->write_row(table->record[0]))) { - if (error != HA_ERR_FOUND_DUPP_KEY && - error != HA_ERR_FOUND_DUPP_UNIQUE) - { - if (create_myisam_from_heap(table, &join->tmp_table_param, error,1)) - DBUG_RETURN(1); // Not a table_is_full error - table->uniques=0; // To ensure rows are the same - } - } - else - { + if (error == HA_ERR_FOUND_DUPP_KEY || + error == HA_ERR_FOUND_DUPP_UNIQUE) + goto end; + if (create_myisam_from_heap(table, &join->tmp_table_param, error,1)) + DBUG_RETURN(1); // Not a table_is_full error + table->uniques=0; // To ensure rows are the same if (++join->send_records >= join->tmp_table_param.end_write_records) DBUG_RETURN(-3); } } } +end: DBUG_RETURN(0); } @@ -4585,6 +4610,7 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), DBUG_RETURN(-2); /* purecov: inspected */ } + join->found_records++; copy_fields(&join->tmp_table_param); // Groups are copied twice. /* Make a key of group index */ for (group=table->group ; group ; group=group->next) @@ -6335,12 +6361,13 @@ static bool add_ref_to_table_cond(THD *thd, JOIN_TAB *join_tab) ** Send a description about what how the select will be done to stdout ****************************************************************************/ -static void select_describe(JOIN *join, bool need_tmp_table, bool need_order) +static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, + bool distinct) { - DBUG_ENTER("select_describe"); - List<Item> field_list; Item *item; + THD *thd=join->thd; + DBUG_ENTER("select_describe"); field_list.push_back(new Item_empty_string("table",NAME_LEN)); field_list.push_back(new Item_empty_string("type",10)); @@ -6356,11 +6383,12 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order) item->maybe_null=1; field_list.push_back(new Item_real("rows",0.0,0,10)); field_list.push_back(new Item_empty_string("Extra",255)); - if (send_fields(join->thd,field_list,1)) + if (send_fields(thd,field_list,1)) return; /* purecov: inspected */ char buff[512],*buff_ptr; - String tmp(buff,sizeof(buff)),*packet= &join->thd->packet; + String tmp(buff,sizeof(buff)),*packet= &thd->packet; + table_map used_tables=0; for (uint i=0 ; i < join->tables ; i++) { JOIN_TAB *tab=join->join_tab+i; @@ -6473,11 +6501,22 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order) } buff_ptr=strmov(buff_ptr,"Using filesort"); } + if (distinct & test_all_bits(used_tables,thd->used_tables)) + { + if (buff != buff_ptr) + { + buff_ptr[0]=';' ; buff_ptr[1]=' '; buff_ptr+=2; + } + buff_ptr=strmov(buff_ptr,"Distinct"); + } net_store_data(packet,buff,(uint) (buff_ptr - buff)); - if (my_net_write(&join->thd->net,(char*) packet->ptr(),packet->length())) + if (my_net_write(&thd->net,(char*) packet->ptr(),packet->length())) DBUG_VOID_RETURN; /* purecov: inspected */ + + // For next iteration + used_tables|=table->map; } - send_eof(&join->thd->net); + send_eof(&thd->net); DBUG_VOID_RETURN; } diff --git a/sql/sql_select.h b/sql/sql_select.h index 8daba5b939e..796802c0a50 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -79,36 +79,36 @@ class JOIN; typedef struct st_join_table { TABLE *table; + KEYUSE *keyuse; /* pointer to first used key */ + SQL_SELECT *select; + COND *select_cond; + QUICK_SELECT *quick; + Item *on_expr; + const char *info; int (*read_first_record)(struct st_join_table *tab); int (*next_select)(JOIN *,struct st_join_table *,bool); - bool cached_eq_ref_table,eq_ref_table; READ_RECORD read_record; - uint keys; /* all keys with can be used */ + double worst_seeks; key_map const_keys; /* Keys with constant part */ key_map checked_keys; /* Keys checked in find_best */ key_map needed_reg; ha_rows records,found_records,read_time; table_map dependent,key_dependent; + uint keys; /* all keys with can be used */ uint use_quick,index; uint status; // Save status for cache - enum join_type type; - JOIN_CACHE cache; - KEYUSE *keyuse; /* pointer to first used key */ - SQL_SELECT *select; - COND *select_cond; - QUICK_SELECT *quick; - Item *on_expr; uint used_fields,used_fieldlength,used_blobs; - const char *info; - double worst_seeks; + enum join_type type; + bool cached_eq_ref_table,eq_ref_table,not_used_in_distinct; TABLE_REF ref; + JOIN_CACHE cache; } JOIN_TAB; typedef struct st_position { /* Used in find_best */ + double records_read; JOIN_TAB *table; KEYUSE *key; - double records_read; } POSITION; @@ -116,16 +116,16 @@ typedef struct st_position { /* Used in find_best */ class TMP_TABLE_PARAM { public: - uint copy_field_count,field_count,sum_func_count,func_count; - uint group_parts,group_length; - uint quick_group; + List<Item> copy_funcs; Copy_field *copy_field; byte *group_buff; - ha_rows end_write_records; Item_result_field **funcs; - List<Item> copy_funcs; MI_COLUMNDEF *recinfo,*start_recinfo; KEY *keyinfo; + ha_rows end_write_records; + uint copy_field_count,field_count,sum_func_count,func_count; + uint group_parts,group_length; + uint quick_group; TMP_TABLE_PARAM() :group_parts(0),group_length(0),copy_field(0) {} ~TMP_TABLE_PARAM() @@ -148,7 +148,7 @@ class JOIN { uint send_group_parts; bool sort_and_group,first_record,full_join,group, no_field_update; table_map const_table_map; - ha_rows send_records; + ha_rows send_records,found_records; POSITION positions[MAX_TABLES+1],best_positions[MAX_TABLES+1]; double best_read; List<Item> *fields; |