- developers - lists.mariadb.org

[Maria-developers] Rev 2814: Better comments in file:///home/psergey/dev/mysql-next/
by Sergey Petrunya 04 Jul '09

04 Jul '09

At file:///home/psergey/dev/mysql-next/ ------------------------------------------------------------ revno: 2814 revision-id: psergey(a)askmonty.org-20090704004450-4pqbx9pm50bzky0l parent: alik(a)sun.com-20090702085822-8svd0aslr7qnddbb committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: mysql-next timestamp: Sat 2009-07-04 04:44:50 +0400 message: Better comments === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-06-30 08:03:05 +0000 +++ b/sql/sql_select.cc 2009-07-04 00:44:50 +0000 @@ -3407,8 +3407,8 @@ sj_nest->sj_on_expr->fix_fields(parent_join->thd, &sj_nest->sj_on_expr); /* - Walk through sj nest's WHERE and ON expressions and call - item->fix_table_changes() for all items. + Fix attributes (mainly item->table_map()) for sj-nest's WHERE and ON + expressions. */ sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr); fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);

1 0

[Maria-developers] [Branch ~maria-captains/maria/5.1] Rev 2715: Added MY_CS_NONASCII marker for character sets that are not compatible with latin1 for characters...
by noreply＠launchpad.net 02 Jul '09

02 Jul '09

------------------------------------------------------------ revno: 2715 committer: Michael Widenius <monty(a)askmonty.org> branch nick: mysql-maria timestamp: Thu 2009-07-02 13:15:33 +0300 message: Added MY_CS_NONASCII marker for character sets that are not compatible with latin1 for characters 0x00-0x7f This allows us to skip and speed up some very common character converts that MySQL is doing when sending data to the client and this gives us a nice speed increase for most queries that uses only characters in the range 0x00-0x7f. This code is based on Alexander Barkov's code that he has done in MySQL 6.0 modified: include/m_ctype.h libmysqld/lib_sql.cc mysys/charset.c scripts/mysql_install_db.sh sql/protocol.cc sql/protocol.h sql/sql_string.cc strings/conf_to_src.c strings/ctype-extra.c strings/ctype-sjis.c strings/ctype-uca.c strings/ctype-ucs2.c strings/ctype-utf8.c strings/ctype.c === modified file 'include/m_ctype.h' --- include/m_ctype.h 2008-12-23 14:21:01 +0000 +++ include/m_ctype.h 2009-07-02 10:15:33 +0000 @@ -87,6 +87,7 @@ #define MY_CS_CSSORT 1024 /* if case sensitive sort order */ #define MY_CS_HIDDEN 2048 /* don't display in SHOW */ #define MY_CS_PUREASCII 4096 /* if a charset is pure ascii */ +#define MY_CS_NONASCII 8192 /* if not ASCII-compatible */ #define MY_CHARSET_UNDEFINED 0 /* Character repertoire flags */ @@ -517,6 +518,7 @@ #define my_strcasecmp(s, a, b) ((s)->coll->strcasecmp((s), (a), (b))) #define my_charpos(cs, b, e, num) (cs)->cset->charpos((cs), (const char*) (b), (const char *)(e), (num)) +my_bool my_charset_is_ascii_compatible(CHARSET_INFO *cs); #define use_mb(s) ((s)->cset->ismbchar != NULL) #define my_ismbchar(s, a, b) ((s)->cset->ismbchar((s), (a), (b))) === modified file 'libmysqld/lib_sql.cc' --- libmysqld/lib_sql.cc 2009-02-24 11:29:49 +0000 +++ libmysqld/lib_sql.cc 2009-07-02 10:15:33 +0000 @@ -1124,6 +1124,7 @@ return false; } + bool Protocol::net_store_data(const uchar *from, size_t length) { char *field_buf; @@ -1143,6 +1144,30 @@ return FALSE; } + +bool Protocol::net_store_data(const uchar *from, size_t length, + CHARSET_INFO *from_cs, CHARSET_INFO *to_cs) +{ + uint conv_length= to_cs->mbmaxlen * length / from_cs->mbminlen; + uint dummy_error; + char *field_buf; + if (!thd->mysql) // bootstrap file handling + return false; + + if (!(field_buf= (char*) alloc_root(alloc, conv_length + sizeof(uint) + 1))) + return true; + *next_field= field_buf + sizeof(uint); + length= copy_and_convert(*next_field, conv_length, to_cs, + (const char*) from, length, from_cs, &dummy_error); + *(uint *) field_buf= length; + (*next_field)[length]= 0; + if (next_mysql_field->max_length < length) + next_mysql_field->max_length= length; + ++next_field; + ++next_mysql_field; + return false; +} + #if defined(_MSC_VER) && _MSC_VER < 1400 #define vsnprintf _vsnprintf #endif === modified file 'mysys/charset.c' --- mysys/charset.c 2009-02-13 16:41:47 +0000 +++ mysys/charset.c 2009-07-02 10:15:33 +0000 @@ -248,6 +248,7 @@ { #if defined(HAVE_CHARSET_ucs2) && defined(HAVE_UCA_COLLATIONS) copy_uca_collation(newcs, &my_charset_ucs2_unicode_ci); + newcs->state|= MY_CS_AVAILABLE | MY_CS_LOADED | MY_CS_NONASCII; #endif } else if (!strcmp(cs->csname, "utf8")) @@ -280,6 +281,8 @@ if (my_charset_is_8bit_pure_ascii(all_charsets[cs->number])) all_charsets[cs->number]->state|= MY_CS_PUREASCII; + if (!my_charset_is_ascii_compatible(cs)) + all_charsets[cs->number]->state|= MY_CS_NONASCII; } } else === modified file 'scripts/mysql_install_db.sh' --- scripts/mysql_install_db.sh 2009-01-06 15:08:15 +0000 +++ scripts/mysql_install_db.sh 2009-07-02 10:15:33 +0000 @@ -1,5 +1,5 @@ #!/bin/sh -# Copyright (C) 2002-2003 MySQL AB +# Copyright (C) 2002-2003 MySQL AB & Monty Program Ab # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -14,7 +14,7 @@ # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -# This scripts creates the MySQL Server system tables +# This scripts creates the MariaDB Server system tables # # All unrecognized arguments to this script are passed to mysqld. @@ -38,26 +38,27 @@ { cat <<EOF Usage: $0 [OPTIONS] - --basedir=path The path to the MySQL installation directory. + --basedir=path The path to the MariaDB installation directory. --builddir=path If using --srcdir with out-of-directory builds, you will need to set this to the location of the build directory where built files reside. - --cross-bootstrap For internal use. Used when building the MySQL system + --cross-bootstrap For internal use. Used when building the MariaDB system tables on a different host than the target. - --datadir=path The path to the MySQL data directory. + --datadir=path The path to the MariaDB data directory. --force Causes mysql_install_db to run even if DNS does not work. In that case, grant table entries that normally use hostnames will use IP addresses. - --ldata=path The path to the MySQL data directory. Same as --datadir. + --ldata=path The path to the MariaDB data directory. Same as + --datadir. --rpm For internal use. This option is used by RPM files - during the MySQL installation process. + during the MariaDB installation process. --skip-name-resolve Use IP addresses rather than hostnames when creating grant table entries. This option can be useful if your DNS does not work. - --srcdir=path The path to the MySQL source directory. This option + --srcdir=path The path to the MariaDB source directory. This option uses the compiled binaries and support files within the source tree, useful for if you don't want to install - MySQL yet and just want to create the system tables. + MariaDB yet and just want to create the system tables. --user=user_name The login username to use for running mysqld. Files and directories created by mysqld will be owned by this user. You must be root to use this option. By default @@ -116,7 +117,7 @@ defaults="$arg" ;; --cross-bootstrap|--windows) - # Used when building the MySQL system tables on a different host than + # Used when building the MariaDB system tables on a different host than # the target. The platform-independent files that are created in # --datadir on the host can be copied to the target system. # @@ -338,10 +339,10 @@ fi echo "WARNING: The host '$hostname' could not be looked up with resolveip." echo "This probably means that your libc libraries are not 100 % compatible" - echo "with this binary MySQL version. The MySQL daemon, mysqld, should work" + echo "with this binary MariaDB version. The MariaDB daemon, mysqld, should work" echo "normally with the exception that host name resolving will not work." echo "This means that you should use IP addresses instead of hostnames" - echo "when specifying MySQL privileges !" + echo "when specifying MariaDB privileges !" fi fi @@ -388,7 +389,7 @@ --net_buffer_length=16K" # Create the system and help tables by passing them to "mysqld --bootstrap" -s_echo "Installing MySQL system tables..." +s_echo "Installing MariaDB/MySQL system tables..." if { echo "use mysql;"; cat $create_system_tables $fill_system_tables; } | eval "$filter_cmd_line" | $mysqld_install_cmd_line > /dev/null then s_echo "OK" @@ -410,14 +411,16 @@ echo "Try 'mysqld --help' if you have problems with paths. Using --log" echo "gives you a log in $ldata that may be helpful." echo - echo "The latest information about MySQL is available on the web at" - echo "http://www.mysql.com/. Please consult the MySQL manual section" + echo "The latest information about MariaDB is available on the web at" + echo "http://askmonty.org/wiki/index.php/MariaDB". + echo "If you have a problem, you can consult the MySQL manual section" echo "'Problems running mysql_install_db', and the manual section that" - echo "describes problems on your OS. Another information source are the" - echo "MySQL email archives available at http://lists.mysql.com/." + echo "describes problems on your OS at http://dev.mysql.com/doc/" + echo "MariaDB is hosted on launchpad; You can find the latest source and" + echo "email lists at http://launchpad.net/maria" echo echo "Please check all of the above before mailing us! And remember, if" - echo "you do mail us, you MUST use the $scriptdir/mysqlbug script!" + echo "you do mail us, you should use the $scriptdir/mysqlbug script!" echo exit 1 fi @@ -442,7 +445,7 @@ s_echo "support-files/mysql.server to the right place for your system" echo - echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !" + echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" echo "To do so, start the server, then issue the following commands:" echo echo "$bindir/mysqladmin -u root password 'new-password'" @@ -455,23 +458,28 @@ echo "databases and anonymous user created by default. This is" echo "strongly recommended for production servers." echo - echo "See the manual for more instructions." + echo "See the MySQL manual for more instructions." if test "$in_rpm" -eq 0 then echo - echo "You can start the MySQL daemon with:" + echo "You can start the MariaDB daemon with:" echo "cd $basedir ; $bindir/mysqld_safe &" echo - echo "You can test the MySQL daemon with mysql-test-run.pl" + echo "You can test the MariaDB daemon with mysql-test-run.pl" echo "cd $basedir/mysql-test ; perl mysql-test-run.pl" fi echo echo "Please report any problems with the $scriptdir/mysqlbug script!" echo - echo "The latest information about MySQL is available at http://www.mysql.com/" - echo "Support MySQL by buying support/licenses from http://shop.mysql.com/" + echo "The latest information about MariaDB is available at http://www.askmonty.org/." + echo "You can find additional information about the MySQL part at:" + echo "http://dev.mysql.com" + echo "Support MariaDB development by buying support/new features from" + echo "Monty Program Ab. You can contact us about this at sales(a)askmonty.org". + echo "Alternatively consider joining our community based development effort:" + echo "http://askmonty.org/wiki/index.php/MariaDB#How_can_I_participate_in_the_dev…" echo fi === modified file 'sql/protocol.cc' --- sql/protocol.cc 2009-04-25 10:05:32 +0000 +++ sql/protocol.cc 2009-07-02 10:15:33 +0000 @@ -58,6 +58,65 @@ } +/* + net_store_data() - extended version with character set conversion. + + It is optimized for short strings whose length after + conversion is garanteed to be less than 251, which accupies + exactly one byte to store length. It allows not to use + the "convert" member as a temporary buffer, conversion + is done directly to the "packet" member. + The limit 251 is good enough to optimize send_fields() + because column, table, database names fit into this limit. +*/ + +#ifndef EMBEDDED_LIBRARY +bool Protocol::net_store_data(const uchar *from, size_t length, + CHARSET_INFO *from_cs, CHARSET_INFO *to_cs) +{ + uint dummy_errors; + /* Calculate maxumum possible result length */ + size_t conv_length= to_cs->mbmaxlen * length / from_cs->mbminlen; + ulong packet_length, new_length; + char *length_pos, *to; + + if (conv_length > 250) + { + /* + For strings with conv_length greater than 250 bytes + we don't know how many bytes we will need to store length: one or two, + because we don't know result length until conversion is done. + For example, when converting from utf8 (mbmaxlen=3) to latin1, + conv_length=300 means that the result length can vary between 100 to 300. + length=100 needs one byte, length=300 needs to bytes. + + Thus conversion directly to "packet" is not worthy. + Let's use "convert" as a temporary buffer. + */ + return (convert->copy((const char*) from, length, from_cs, to_cs, + &dummy_errors) || + net_store_data((const uchar*) convert->ptr(), convert->length())); + } + + packet_length= packet->length(); + new_length= packet_length + conv_length + 1; + + if (new_length > packet->alloced_length() && packet->realloc(new_length)) + return 1; + + length_pos= (char*) packet->ptr() + packet_length; + to= length_pos + 1; + + to+= copy_and_convert(to, conv_length, to_cs, + (const char*) from, length, from_cs, &dummy_errors); + + net_store_length((uchar*) length_pos, to - length_pos - 1); + packet->length((uint) (to - packet->ptr())); + return 0; +} +#endif + + /** Send a error string to client. @@ -773,10 +832,10 @@ fromcs != &my_charset_bin && tocs != &my_charset_bin) { - uint dummy_errors; - return (convert->copy(from, length, fromcs, tocs, &dummy_errors) || - net_store_data((uchar*) convert->ptr(), convert->length())); + /* Store with conversion */ + return net_store_data((uchar*) from, length, fromcs, tocs); } + /* Store without conversion */ return net_store_data((uchar*) from, length); } @@ -802,7 +861,7 @@ { CHARSET_INFO *tocs= this->thd->variables.character_set_results; #ifndef DBUG_OFF - DBUG_PRINT("info", ("Protocol_text::store field %u (%u): %*s", field_pos, + DBUG_PRINT("info", ("Protocol_text::store field %u (%u): %.*s", field_pos, field_count, (int) length, from)); DBUG_ASSERT(field_pos < field_count); DBUG_ASSERT(field_types == 0 || === modified file 'sql/protocol.h' --- sql/protocol.h 2007-12-20 21:11:37 +0000 +++ sql/protocol.h 2009-07-02 10:15:33 +0000 @@ -42,6 +42,8 @@ MYSQL_FIELD *next_mysql_field; MEM_ROOT *alloc; #endif + bool net_store_data(const uchar *from, size_t length, + CHARSET_INFO *fromcs, CHARSET_INFO *tocs); bool store_string_aux(const char *from, size_t length, CHARSET_INFO *fromcs, CHARSET_INFO *tocs); public: === modified file 'sql/sql_string.cc' --- sql/sql_string.cc 2009-04-25 10:05:32 +0000 +++ sql/sql_string.cc 2009-07-02 10:15:33 +0000 @@ -782,10 +782,11 @@ */ -uint32 -copy_and_convert(char *to, uint32 to_length, CHARSET_INFO *to_cs, - const char *from, uint32 from_length, CHARSET_INFO *from_cs, - uint *errors) +static uint32 +copy_and_convert_extended(char *to, uint32 to_length, CHARSET_INFO *to_cs, + const char *from, uint32 from_length, + CHARSET_INFO *from_cs, + uint *errors) { int cnvres; my_wc_t wc; @@ -900,6 +901,65 @@ } /* + Optimized for quick copying of ASCII characters in the range 0x00..0x7F. +*/ +uint32 +copy_and_convert(char *to, uint32 to_length, CHARSET_INFO *to_cs, + const char *from, uint32 from_length, CHARSET_INFO *from_cs, + uint *errors) +{ + /* + If any of the character sets is not ASCII compatible, + immediately switch to slow mb_wc->wc_mb method. + */ + if ((to_cs->state | from_cs->state) & MY_CS_NONASCII) + return copy_and_convert_extended(to, to_length, to_cs, + from, from_length, from_cs, errors); + + uint32 length= min(to_length, from_length), length2= length; + +#if defined(__i386__) + /* + Special loop for i386, it allows to refer to a + non-aligned memory block as UINT32, which makes + it possible to copy four bytes at once. This + gives about 10% performance improvement comparing + to byte-by-byte loop. + */ + for ( ; length >= 4; length-= 4, from+= 4, to+= 4) + { + if ((*(uint32*)from) & 0x80808080) + break; + *((uint32*) to)= *((const uint32*) from); + } +#endif + + for (; ; *to++= *from++, length--) + { + if (!length) + { + *errors= 0; + return length2; + } + if (*((unsigned char*) from) > 0x7F) /* A non-ASCII character */ + { + uint32 copied_length= length2 - length; + to_length-= copied_length; + from_length-= copied_length; + return copied_length + copy_and_convert_extended(to, to_length, + to_cs, + from, from_length, + from_cs, + errors); + } + } + + DBUG_ASSERT(FALSE); // Should never get to here + return 0; // Make compiler happy +} + + +/* copy a string, with optional character set conversion, with optional left padding (for binary -> UCS2 conversion) === modified file 'strings/conf_to_src.c' --- strings/conf_to_src.c 2008-11-14 16:29:38 +0000 +++ strings/conf_to_src.c 2009-07-02 10:15:33 +0000 @@ -184,11 +184,12 @@ { fprintf(f,"{\n"); fprintf(f," %d,%d,%d,\n",cs->number,0,0); - fprintf(f," MY_CS_COMPILED%s%s%s%s,\n", + fprintf(f," MY_CS_COMPILED%s%s%s%s%s,\n", cs->state & MY_CS_BINSORT ? "|MY_CS_BINSORT" : "", cs->state & MY_CS_PRIMARY ? "|MY_CS_PRIMARY" : "", is_case_sensitive(cs) ? "|MY_CS_CSSORT" : "", - my_charset_is_8bit_pure_ascii(cs) ? "|MY_CS_PUREASCII" : ""); + my_charset_is_8bit_pure_ascii(cs) ? "|MY_CS_PUREASCII" : "", + !my_charset_is_ascii_compatible(cs) ? "|MY_CS_NONASCII": ""); if (cs->name) { === modified file 'strings/ctype-extra.c' --- strings/ctype-extra.c 2007-08-20 11:47:31 +0000 +++ strings/ctype-extra.c 2009-07-02 10:15:33 +0000 @@ -6804,7 +6804,7 @@ #ifdef HAVE_CHARSET_swe7 { 10,0,0, - MY_CS_COMPILED|MY_CS_PRIMARY, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_NONASCII, "swe7", /* cset name */ "swe7_swedish_ci", /* coll name */ "", /* comment */ @@ -8454,7 +8454,7 @@ #ifdef HAVE_CHARSET_swe7 { 82,0,0, - MY_CS_COMPILED|MY_CS_BINSORT, + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_NONASCII, "swe7", /* cset name */ "swe7_bin", /* coll name */ "", /* comment */ @@ -8550,72 +8550,6 @@ } , #endif -#ifdef HAVE_CHARSET_geostd8 -{ - 92,0,0, - MY_CS_COMPILED|MY_CS_PRIMARY, - "geostd8", /* cset name */ - "geostd8_general_ci", /* coll name */ - "", /* comment */ - NULL, /* tailoring */ - ctype_geostd8_general_ci, /* ctype */ - to_lower_geostd8_general_ci, /* lower */ - to_upper_geostd8_general_ci, /* upper */ - sort_order_geostd8_general_ci, /* sort_order */ - NULL, /* contractions */ - NULL, /* sort_order_big*/ - to_uni_geostd8_general_ci, /* to_uni */ - NULL, /* from_uni */ - my_unicase_default, /* caseinfo */ - NULL, /* state map */ - NULL, /* ident map */ - 1, /* strxfrm_multiply*/ - 1, /* caseup_multiply*/ - 1, /* casedn_multiply*/ - 1, /* mbminlen */ - 1, /* mbmaxlen */ - 0, /* min_sort_char */ - 255, /* max_sort_char */ - ' ', /* pad_char */ - 0, /* escape_with_backslash_is_dangerous */ - &my_charset_8bit_handler, - &my_collation_8bit_simple_ci_handler, -} -, -#endif -#ifdef HAVE_CHARSET_geostd8 -{ - 93,0,0, - MY_CS_COMPILED|MY_CS_BINSORT, - "geostd8", /* cset name */ - "geostd8_bin", /* coll name */ - "", /* comment */ - NULL, /* tailoring */ - ctype_geostd8_bin, /* ctype */ - to_lower_geostd8_bin, /* lower */ - to_upper_geostd8_bin, /* upper */ - NULL, /* sort_order */ - NULL, /* contractions */ - NULL, /* sort_order_big*/ - to_uni_geostd8_bin, /* to_uni */ - NULL, /* from_uni */ - my_unicase_default, /* caseinfo */ - NULL, /* state map */ - NULL, /* ident map */ - 1, /* strxfrm_multiply*/ - 1, /* caseup_multiply*/ - 1, /* casedn_multiply*/ - 1, /* mbminlen */ - 1, /* mbmaxlen */ - 0, /* min_sort_char */ - 255, /* max_sort_char */ - ' ', /* pad_char */ - 0, /* escape_with_backslash_is_dangerous */ - &my_charset_8bit_handler, - &my_collation_8bit_bin_handler, -} -, -#endif #ifdef HAVE_CHARSET_latin1 { 94,0,0, === modified file 'strings/ctype-sjis.c' --- strings/ctype-sjis.c 2007-10-04 07:10:15 +0000 +++ strings/ctype-sjis.c 2009-07-02 10:15:33 +0000 @@ -4672,7 +4672,7 @@ CHARSET_INFO my_charset_sjis_japanese_ci= { 13,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM, /* state */ + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_NONASCII, /* state */ "sjis", /* cs name */ "sjis_japanese_ci", /* name */ "", /* comment */ @@ -4704,7 +4704,7 @@ CHARSET_INFO my_charset_sjis_bin= { 88,0,0, /* number */ - MY_CS_COMPILED|MY_CS_BINSORT, /* state */ + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_NONASCII, /* state */ "sjis", /* cs name */ "sjis_bin", /* name */ "", /* comment */ === modified file 'strings/ctype-uca.c' --- strings/ctype-uca.c 2007-07-03 09:06:57 +0000 +++ strings/ctype-uca.c 2009-07-02 10:15:33 +0000 @@ -8086,7 +8086,7 @@ CHARSET_INFO my_charset_ucs2_unicode_ci= { 128,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_unicode_ci", /* name */ "", /* comment */ @@ -8118,7 +8118,7 @@ CHARSET_INFO my_charset_ucs2_icelandic_uca_ci= { 129,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_icelandic_ci",/* name */ "", /* comment */ @@ -8150,7 +8150,7 @@ CHARSET_INFO my_charset_ucs2_latvian_uca_ci= { 130,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_latvian_ci", /* name */ "", /* comment */ @@ -8182,7 +8182,7 @@ CHARSET_INFO my_charset_ucs2_romanian_uca_ci= { 131,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_romanian_ci", /* name */ "", /* comment */ @@ -8214,7 +8214,7 @@ CHARSET_INFO my_charset_ucs2_slovenian_uca_ci= { 132,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_slovenian_ci",/* name */ "", /* comment */ @@ -8246,7 +8246,7 @@ CHARSET_INFO my_charset_ucs2_polish_uca_ci= { 133,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_polish_ci", /* name */ "", /* comment */ @@ -8278,7 +8278,7 @@ CHARSET_INFO my_charset_ucs2_estonian_uca_ci= { 134,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_estonian_ci", /* name */ "", /* comment */ @@ -8310,7 +8310,7 @@ CHARSET_INFO my_charset_ucs2_spanish_uca_ci= { 135,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_spanish_ci", /* name */ "", /* comment */ @@ -8342,7 +8342,7 @@ CHARSET_INFO my_charset_ucs2_swedish_uca_ci= { 136,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_swedish_ci", /* name */ "", /* comment */ @@ -8374,7 +8374,7 @@ CHARSET_INFO my_charset_ucs2_turkish_uca_ci= { 137,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_turkish_ci", /* name */ "", /* comment */ @@ -8406,7 +8406,7 @@ CHARSET_INFO my_charset_ucs2_czech_uca_ci= { 138,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_czech_ci", /* name */ "", /* comment */ @@ -8439,7 +8439,7 @@ CHARSET_INFO my_charset_ucs2_danish_uca_ci= { 139,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_danish_ci", /* name */ "", /* comment */ @@ -8471,7 +8471,7 @@ CHARSET_INFO my_charset_ucs2_lithuanian_uca_ci= { 140,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_lithuanian_ci",/* name */ "", /* comment */ @@ -8503,7 +8503,7 @@ CHARSET_INFO my_charset_ucs2_slovak_uca_ci= { 141,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_slovak_ci", /* name */ "", /* comment */ @@ -8535,7 +8535,7 @@ CHARSET_INFO my_charset_ucs2_spanish2_uca_ci= { 142,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_spanish2_ci", /* name */ "", /* comment */ @@ -8568,7 +8568,7 @@ CHARSET_INFO my_charset_ucs2_roman_uca_ci= { 143,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_roman_ci", /* name */ "", /* comment */ @@ -8601,7 +8601,7 @@ CHARSET_INFO my_charset_ucs2_persian_uca_ci= { 144,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_persian_ci", /* name */ "", /* comment */ @@ -8634,7 +8634,7 @@ CHARSET_INFO my_charset_ucs2_esperanto_uca_ci= { 145,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_esperanto_ci",/* name */ "", /* comment */ @@ -8667,7 +8667,7 @@ CHARSET_INFO my_charset_ucs2_hungarian_uca_ci= { 146,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_hungarian_ci",/* name */ "", /* comment */ === modified file 'strings/ctype-ucs2.c' --- strings/ctype-ucs2.c 2009-02-13 16:41:47 +0000 +++ strings/ctype-ucs2.c 2009-07-02 10:15:33 +0000 @@ -1717,7 +1717,7 @@ CHARSET_INFO my_charset_ucs2_general_ci= { 35,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_general_ci", /* name */ "", /* comment */ @@ -1749,7 +1749,7 @@ CHARSET_INFO my_charset_ucs2_bin= { 90,0,0, /* number */ - MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_bin", /* name */ "", /* comment */ === modified file 'strings/ctype-utf8.c' --- strings/ctype-utf8.c 2008-02-11 12:28:33 +0000 +++ strings/ctype-utf8.c 2009-07-02 10:15:33 +0000 @@ -4204,7 +4204,7 @@ CHARSET_INFO my_charset_filename= { 17,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_HIDDEN, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_HIDDEN|MY_CS_NONASCII, "filename", /* cs name */ "filename", /* name */ "", /* comment */ === modified file 'strings/ctype.c' --- strings/ctype.c 2009-04-25 10:05:32 +0000 +++ strings/ctype.c 2009-07-02 10:15:33 +0000 @@ -405,3 +405,23 @@ } return 1; } + + +/* + Shared function between conf_to_src and mysys. + Check if a 8bit character set is compatible with + ascii on the range 0x00..0x7F. +*/ +my_bool +my_charset_is_ascii_compatible(CHARSET_INFO *cs) +{ + uint i; + if (!cs->tab_to_uni) + return 1; + for (i= 0; i < 128; i++) + { + if (cs->tab_to_uni[i] != i) + return 0; + } + return 1; +} -- lp:maria https://code.launchpad.net/~maria-captains/maria/5.1 Your team Maria developers is subscribed to branch lp:maria. To unsubscribe from this branch go to https://code.launchpad.net/~maria-captains/maria/5.1/+edit-subscription.

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2715)
by Michael Widenius 02 Jul '09

02 Jul '09

#At lp:maria based on revid:monty@askmonty.org-20090630120129-6gan4k9dyjxj83e4 2715 Michael Widenius 2009-07-02 Added MY_CS_NONASCII marker for character sets that are not compatible with latin1 for characters 0x00-0x7f This allows us to skip and speed up some very common character converts that MySQL is doing when sending data to the client and this gives us a nice speed increase for most queries that uses only characters in the range 0x00-0x7f. This code is based on Alexander Barkov's code that he has done in MySQL 6.0 modified: include/m_ctype.h libmysqld/lib_sql.cc mysys/charset.c scripts/mysql_install_db.sh sql/protocol.cc sql/protocol.h sql/sql_string.cc strings/conf_to_src.c strings/ctype-extra.c strings/ctype-sjis.c strings/ctype-uca.c strings/ctype-ucs2.c strings/ctype-utf8.c strings/ctype.c per-file messages: include/m_ctype.h Added MY_CS_NONASCII marker libmysqld/lib_sql.cc Added function net_store_data(...) that takes to and from CHARSET_INFO * as arguments mysys/charset.c Mark character sets with MY_CS_NONASCII scripts/mysql_install_db.sh Fixed messages to refer to MariaDB instead of MySQL sql/protocol.cc Added function net_store_data(...) that takes to and from CHARSET_INFO * as arguments sql/protocol.h Added function net_store_data(...) that takes to and from CHARSET_INFO * as arguments sql/sql_string.cc Quicker copy of strings with no characters above 0x7f strings/conf_to_src.c Added printing of MY_CS_NONASCII strings/ctype-extra.c Mark incompatible character sets with MY_CS_NONASCII Removed duplicated character set geostd strings/ctype-sjis.c Mark incompatible character sets with MY_CS_NONASCII strings/ctype-uca.c Mark incompatible character sets with MY_CS_NONASCII strings/ctype-ucs2.c Mark incompatible character sets with MY_CS_NONASCII strings/ctype-utf8.c Mark incompatible character sets with MY_CS_NONASCII strings/ctype.c Added function to check if character set is compatible with latin1 in ranges 0x00-0x7f === modified file 'include/m_ctype.h' --- a/include/m_ctype.h 2008-12-23 14:21:01 +0000 +++ b/include/m_ctype.h 2009-07-02 10:15:33 +0000 @@ -87,6 +87,7 @@ extern MY_UNI_CTYPE my_uni_ctype[256]; #define MY_CS_CSSORT 1024 /* if case sensitive sort order */ #define MY_CS_HIDDEN 2048 /* don't display in SHOW */ #define MY_CS_PUREASCII 4096 /* if a charset is pure ascii */ +#define MY_CS_NONASCII 8192 /* if not ASCII-compatible */ #define MY_CHARSET_UNDEFINED 0 /* Character repertoire flags */ @@ -517,6 +518,7 @@ uint my_charset_repertoire(CHARSET_INFO #define my_strcasecmp(s, a, b) ((s)->coll->strcasecmp((s), (a), (b))) #define my_charpos(cs, b, e, num) (cs)->cset->charpos((cs), (const char*) (b), (const char *)(e), (num)) +my_bool my_charset_is_ascii_compatible(CHARSET_INFO *cs); #define use_mb(s) ((s)->cset->ismbchar != NULL) #define my_ismbchar(s, a, b) ((s)->cset->ismbchar((s), (a), (b))) === modified file 'libmysqld/lib_sql.cc' --- a/libmysqld/lib_sql.cc 2009-02-24 11:29:49 +0000 +++ b/libmysqld/lib_sql.cc 2009-07-02 10:15:33 +0000 @@ -1124,6 +1124,7 @@ bool Protocol_text::store_null() return false; } + bool Protocol::net_store_data(const uchar *from, size_t length) { char *field_buf; @@ -1143,6 +1144,30 @@ bool Protocol::net_store_data(const ucha return FALSE; } + +bool Protocol::net_store_data(const uchar *from, size_t length, + CHARSET_INFO *from_cs, CHARSET_INFO *to_cs) +{ + uint conv_length= to_cs->mbmaxlen * length / from_cs->mbminlen; + uint dummy_error; + char *field_buf; + if (!thd->mysql) // bootstrap file handling + return false; + + if (!(field_buf= (char*) alloc_root(alloc, conv_length + sizeof(uint) + 1))) + return true; + *next_field= field_buf + sizeof(uint); + length= copy_and_convert(*next_field, conv_length, to_cs, + (const char*) from, length, from_cs, &dummy_error); + *(uint *) field_buf= length; + (*next_field)[length]= 0; + if (next_mysql_field->max_length < length) + next_mysql_field->max_length= length; + ++next_field; + ++next_mysql_field; + return false; +} + #if defined(_MSC_VER) && _MSC_VER < 1400 #define vsnprintf _vsnprintf #endif === modified file 'mysys/charset.c' --- a/mysys/charset.c 2009-02-13 16:41:47 +0000 +++ b/mysys/charset.c 2009-07-02 10:15:33 +0000 @@ -248,6 +248,7 @@ static int add_collation(CHARSET_INFO *c { #if defined(HAVE_CHARSET_ucs2) && defined(HAVE_UCA_COLLATIONS) copy_uca_collation(newcs, &my_charset_ucs2_unicode_ci); + newcs->state|= MY_CS_AVAILABLE | MY_CS_LOADED | MY_CS_NONASCII; #endif } else if (!strcmp(cs->csname, "utf8")) @@ -280,6 +281,8 @@ static int add_collation(CHARSET_INFO *c if (my_charset_is_8bit_pure_ascii(all_charsets[cs->number])) all_charsets[cs->number]->state|= MY_CS_PUREASCII; + if (!my_charset_is_ascii_compatible(cs)) + all_charsets[cs->number]->state|= MY_CS_NONASCII; } } else === modified file 'scripts/mysql_install_db.sh' --- a/scripts/mysql_install_db.sh 2009-01-06 15:08:15 +0000 +++ b/scripts/mysql_install_db.sh 2009-07-02 10:15:33 +0000 @@ -1,5 +1,5 @@ #!/bin/sh -# Copyright (C) 2002-2003 MySQL AB +# Copyright (C) 2002-2003 MySQL AB & Monty Program Ab # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -14,7 +14,7 @@ # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -# This scripts creates the MySQL Server system tables +# This scripts creates the MariaDB Server system tables # # All unrecognized arguments to this script are passed to mysqld. @@ -38,26 +38,27 @@ usage() { cat <<EOF Usage: $0 [OPTIONS] - --basedir=path The path to the MySQL installation directory. + --basedir=path The path to the MariaDB installation directory. --builddir=path If using --srcdir with out-of-directory builds, you will need to set this to the location of the build directory where built files reside. - --cross-bootstrap For internal use. Used when building the MySQL system + --cross-bootstrap For internal use. Used when building the MariaDB system tables on a different host than the target. - --datadir=path The path to the MySQL data directory. + --datadir=path The path to the MariaDB data directory. --force Causes mysql_install_db to run even if DNS does not work. In that case, grant table entries that normally use hostnames will use IP addresses. - --ldata=path The path to the MySQL data directory. Same as --datadir. + --ldata=path The path to the MariaDB data directory. Same as + --datadir. --rpm For internal use. This option is used by RPM files - during the MySQL installation process. + during the MariaDB installation process. --skip-name-resolve Use IP addresses rather than hostnames when creating grant table entries. This option can be useful if your DNS does not work. - --srcdir=path The path to the MySQL source directory. This option + --srcdir=path The path to the MariaDB source directory. This option uses the compiled binaries and support files within the source tree, useful for if you don't want to install - MySQL yet and just want to create the system tables. + MariaDB yet and just want to create the system tables. --user=user_name The login username to use for running mysqld. Files and directories created by mysqld will be owned by this user. You must be root to use this option. By default @@ -116,7 +117,7 @@ parse_arguments() defaults="$arg" ;; --cross-bootstrap|--windows) - # Used when building the MySQL system tables on a different host than + # Used when building the MariaDB system tables on a different host than # the target. The platform-independent files that are created in # --datadir on the host can be copied to the target system. # @@ -338,10 +339,10 @@ then fi echo "WARNING: The host '$hostname' could not be looked up with resolveip." echo "This probably means that your libc libraries are not 100 % compatible" - echo "with this binary MySQL version. The MySQL daemon, mysqld, should work" + echo "with this binary MariaDB version. The MariaDB daemon, mysqld, should work" echo "normally with the exception that host name resolving will not work." echo "This means that you should use IP addresses instead of hostnames" - echo "when specifying MySQL privileges !" + echo "when specifying MariaDB privileges !" fi fi @@ -388,7 +389,7 @@ mysqld_install_cmd_line="$mysqld_bootstr --net_buffer_length=16K" # Create the system and help tables by passing them to "mysqld --bootstrap" -s_echo "Installing MySQL system tables..." +s_echo "Installing MariaDB/MySQL system tables..." if { echo "use mysql;"; cat $create_system_tables $fill_system_tables; } | eval "$filter_cmd_line" | $mysqld_install_cmd_line > /dev/null then s_echo "OK" @@ -410,14 +411,16 @@ else echo "Try 'mysqld --help' if you have problems with paths. Using --log" echo "gives you a log in $ldata that may be helpful." echo - echo "The latest information about MySQL is available on the web at" - echo "http://www.mysql.com/. Please consult the MySQL manual section" + echo "The latest information about MariaDB is available on the web at" + echo "http://askmonty.org/wiki/index.php/MariaDB". + echo "If you have a problem, you can consult the MySQL manual section" echo "'Problems running mysql_install_db', and the manual section that" - echo "describes problems on your OS. Another information source are the" - echo "MySQL email archives available at http://lists.mysql.com/." + echo "describes problems on your OS at http://dev.mysql.com/doc/" + echo "MariaDB is hosted on launchpad; You can find the latest source and" + echo "email lists at http://launchpad.net/maria" echo echo "Please check all of the above before mailing us! And remember, if" - echo "you do mail us, you MUST use the $scriptdir/mysqlbug script!" + echo "you do mail us, you should use the $scriptdir/mysqlbug script!" echo exit 1 fi @@ -442,7 +445,7 @@ then s_echo "support-files/mysql.server to the right place for your system" echo - echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !" + echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !" echo "To do so, start the server, then issue the following commands:" echo echo "$bindir/mysqladmin -u root password 'new-password'" @@ -455,23 +458,28 @@ then echo "databases and anonymous user created by default. This is" echo "strongly recommended for production servers." echo - echo "See the manual for more instructions." + echo "See the MySQL manual for more instructions." if test "$in_rpm" -eq 0 then echo - echo "You can start the MySQL daemon with:" + echo "You can start the MariaDB daemon with:" echo "cd $basedir ; $bindir/mysqld_safe &" echo - echo "You can test the MySQL daemon with mysql-test-run.pl" + echo "You can test the MariaDB daemon with mysql-test-run.pl" echo "cd $basedir/mysql-test ; perl mysql-test-run.pl" fi echo echo "Please report any problems with the $scriptdir/mysqlbug script!" echo - echo "The latest information about MySQL is available at http://www.mysql.com/" - echo "Support MySQL by buying support/licenses from http://shop.mysql.com/" + echo "The latest information about MariaDB is available at http://www.askmonty.org/." + echo "You can find additional information about the MySQL part at:" + echo "http://dev.mysql.com" + echo "Support MariaDB development by buying support/new features from" + echo "Monty Program Ab. You can contact us about this at sales(a)askmonty.org". + echo "Alternatively consider joining our community based development effort:" + echo "http://askmonty.org/wiki/index.php/MariaDB#How_can_I_participate_in_the_dev…" echo fi === modified file 'sql/protocol.cc' --- a/sql/protocol.cc 2009-04-25 10:05:32 +0000 +++ b/sql/protocol.cc 2009-07-02 10:15:33 +0000 @@ -58,6 +58,65 @@ bool Protocol_binary::net_store_data(con } +/* + net_store_data() - extended version with character set conversion. + + It is optimized for short strings whose length after + conversion is garanteed to be less than 251, which accupies + exactly one byte to store length. It allows not to use + the "convert" member as a temporary buffer, conversion + is done directly to the "packet" member. + The limit 251 is good enough to optimize send_fields() + because column, table, database names fit into this limit. +*/ + +#ifndef EMBEDDED_LIBRARY +bool Protocol::net_store_data(const uchar *from, size_t length, + CHARSET_INFO *from_cs, CHARSET_INFO *to_cs) +{ + uint dummy_errors; + /* Calculate maxumum possible result length */ + size_t conv_length= to_cs->mbmaxlen * length / from_cs->mbminlen; + ulong packet_length, new_length; + char *length_pos, *to; + + if (conv_length > 250) + { + /* + For strings with conv_length greater than 250 bytes + we don't know how many bytes we will need to store length: one or two, + because we don't know result length until conversion is done. + For example, when converting from utf8 (mbmaxlen=3) to latin1, + conv_length=300 means that the result length can vary between 100 to 300. + length=100 needs one byte, length=300 needs to bytes. + + Thus conversion directly to "packet" is not worthy. + Let's use "convert" as a temporary buffer. + */ + return (convert->copy((const char*) from, length, from_cs, to_cs, + &dummy_errors) || + net_store_data((const uchar*) convert->ptr(), convert->length())); + } + + packet_length= packet->length(); + new_length= packet_length + conv_length + 1; + + if (new_length > packet->alloced_length() && packet->realloc(new_length)) + return 1; + + length_pos= (char*) packet->ptr() + packet_length; + to= length_pos + 1; + + to+= copy_and_convert(to, conv_length, to_cs, + (const char*) from, length, from_cs, &dummy_errors); + + net_store_length((uchar*) length_pos, to - length_pos - 1); + packet->length((uint) (to - packet->ptr())); + return 0; +} +#endif + + /** Send a error string to client. @@ -773,10 +832,10 @@ bool Protocol::store_string_aux(const ch fromcs != &my_charset_bin && tocs != &my_charset_bin) { - uint dummy_errors; - return (convert->copy(from, length, fromcs, tocs, &dummy_errors) || - net_store_data((uchar*) convert->ptr(), convert->length())); + /* Store with conversion */ + return net_store_data((uchar*) from, length, fromcs, tocs); } + /* Store without conversion */ return net_store_data((uchar*) from, length); } @@ -802,7 +861,7 @@ bool Protocol_text::store(const char *fr { CHARSET_INFO *tocs= this->thd->variables.character_set_results; #ifndef DBUG_OFF - DBUG_PRINT("info", ("Protocol_text::store field %u (%u): %*s", field_pos, + DBUG_PRINT("info", ("Protocol_text::store field %u (%u): %.*s", field_pos, field_count, (int) length, from)); DBUG_ASSERT(field_pos < field_count); DBUG_ASSERT(field_types == 0 || === modified file 'sql/protocol.h' --- a/sql/protocol.h 2007-12-20 21:11:37 +0000 +++ b/sql/protocol.h 2009-07-02 10:15:33 +0000 @@ -42,6 +42,8 @@ protected: MYSQL_FIELD *next_mysql_field; MEM_ROOT *alloc; #endif + bool net_store_data(const uchar *from, size_t length, + CHARSET_INFO *fromcs, CHARSET_INFO *tocs); bool store_string_aux(const char *from, size_t length, CHARSET_INFO *fromcs, CHARSET_INFO *tocs); public: === modified file 'sql/sql_string.cc' --- a/sql/sql_string.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_string.cc 2009-07-02 10:15:33 +0000 @@ -782,10 +782,11 @@ String *copy_if_not_alloced(String *to,S */ -uint32 -copy_and_convert(char *to, uint32 to_length, CHARSET_INFO *to_cs, - const char *from, uint32 from_length, CHARSET_INFO *from_cs, - uint *errors) +static uint32 +copy_and_convert_extended(char *to, uint32 to_length, CHARSET_INFO *to_cs, + const char *from, uint32 from_length, + CHARSET_INFO *from_cs, + uint *errors) { int cnvres; my_wc_t wc; @@ -900,6 +901,65 @@ my_copy_with_hex_escaping(CHARSET_INFO * } /* + Optimized for quick copying of ASCII characters in the range 0x00..0x7F. +*/ +uint32 +copy_and_convert(char *to, uint32 to_length, CHARSET_INFO *to_cs, + const char *from, uint32 from_length, CHARSET_INFO *from_cs, + uint *errors) +{ + /* + If any of the character sets is not ASCII compatible, + immediately switch to slow mb_wc->wc_mb method. + */ + if ((to_cs->state | from_cs->state) & MY_CS_NONASCII) + return copy_and_convert_extended(to, to_length, to_cs, + from, from_length, from_cs, errors); + + uint32 length= min(to_length, from_length), length2= length; + +#if defined(__i386__) + /* + Special loop for i386, it allows to refer to a + non-aligned memory block as UINT32, which makes + it possible to copy four bytes at once. This + gives about 10% performance improvement comparing + to byte-by-byte loop. + */ + for ( ; length >= 4; length-= 4, from+= 4, to+= 4) + { + if ((*(uint32*)from) & 0x80808080) + break; + *((uint32*) to)= *((const uint32*) from); + } +#endif + + for (; ; *to++= *from++, length--) + { + if (!length) + { + *errors= 0; + return length2; + } + if (*((unsigned char*) from) > 0x7F) /* A non-ASCII character */ + { + uint32 copied_length= length2 - length; + to_length-= copied_length; + from_length-= copied_length; + return copied_length + copy_and_convert_extended(to, to_length, + to_cs, + from, from_length, + from_cs, + errors); + } + } + + DBUG_ASSERT(FALSE); // Should never get to here + return 0; // Make compiler happy +} + + +/* copy a string, with optional character set conversion, with optional left padding (for binary -> UCS2 conversion) === modified file 'strings/conf_to_src.c' --- a/strings/conf_to_src.c 2008-11-14 16:29:38 +0000 +++ b/strings/conf_to_src.c 2009-07-02 10:15:33 +0000 @@ -184,11 +184,12 @@ void dispcset(FILE *f,CHARSET_INFO *cs) { fprintf(f,"{\n"); fprintf(f," %d,%d,%d,\n",cs->number,0,0); - fprintf(f," MY_CS_COMPILED%s%s%s%s,\n", + fprintf(f," MY_CS_COMPILED%s%s%s%s%s,\n", cs->state & MY_CS_BINSORT ? "|MY_CS_BINSORT" : "", cs->state & MY_CS_PRIMARY ? "|MY_CS_PRIMARY" : "", is_case_sensitive(cs) ? "|MY_CS_CSSORT" : "", - my_charset_is_8bit_pure_ascii(cs) ? "|MY_CS_PUREASCII" : ""); + my_charset_is_8bit_pure_ascii(cs) ? "|MY_CS_PUREASCII" : "", + !my_charset_is_ascii_compatible(cs) ? "|MY_CS_NONASCII": ""); if (cs->name) { === modified file 'strings/ctype-extra.c' --- a/strings/ctype-extra.c 2007-08-20 11:47:31 +0000 +++ b/strings/ctype-extra.c 2009-07-02 10:15:33 +0000 @@ -6804,7 +6804,7 @@ CHARSET_INFO compiled_charsets[] = { #ifdef HAVE_CHARSET_swe7 { 10,0,0, - MY_CS_COMPILED|MY_CS_PRIMARY, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_NONASCII, "swe7", /* cset name */ "swe7_swedish_ci", /* coll name */ "", /* comment */ @@ -8454,7 +8454,7 @@ CHARSET_INFO compiled_charsets[] = { #ifdef HAVE_CHARSET_swe7 { 82,0,0, - MY_CS_COMPILED|MY_CS_BINSORT, + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_NONASCII, "swe7", /* cset name */ "swe7_bin", /* coll name */ "", /* comment */ @@ -8550,72 +8550,6 @@ CHARSET_INFO compiled_charsets[] = { } , #endif -#ifdef HAVE_CHARSET_geostd8 -{ - 92,0,0, - MY_CS_COMPILED|MY_CS_PRIMARY, - "geostd8", /* cset name */ - "geostd8_general_ci", /* coll name */ - "", /* comment */ - NULL, /* tailoring */ - ctype_geostd8_general_ci, /* ctype */ - to_lower_geostd8_general_ci, /* lower */ - to_upper_geostd8_general_ci, /* upper */ - sort_order_geostd8_general_ci, /* sort_order */ - NULL, /* contractions */ - NULL, /* sort_order_big*/ - to_uni_geostd8_general_ci, /* to_uni */ - NULL, /* from_uni */ - my_unicase_default, /* caseinfo */ - NULL, /* state map */ - NULL, /* ident map */ - 1, /* strxfrm_multiply*/ - 1, /* caseup_multiply*/ - 1, /* casedn_multiply*/ - 1, /* mbminlen */ - 1, /* mbmaxlen */ - 0, /* min_sort_char */ - 255, /* max_sort_char */ - ' ', /* pad_char */ - 0, /* escape_with_backslash_is_dangerous */ - &my_charset_8bit_handler, - &my_collation_8bit_simple_ci_handler, -} -, -#endif -#ifdef HAVE_CHARSET_geostd8 -{ - 93,0,0, - MY_CS_COMPILED|MY_CS_BINSORT, - "geostd8", /* cset name */ - "geostd8_bin", /* coll name */ - "", /* comment */ - NULL, /* tailoring */ - ctype_geostd8_bin, /* ctype */ - to_lower_geostd8_bin, /* lower */ - to_upper_geostd8_bin, /* upper */ - NULL, /* sort_order */ - NULL, /* contractions */ - NULL, /* sort_order_big*/ - to_uni_geostd8_bin, /* to_uni */ - NULL, /* from_uni */ - my_unicase_default, /* caseinfo */ - NULL, /* state map */ - NULL, /* ident map */ - 1, /* strxfrm_multiply*/ - 1, /* caseup_multiply*/ - 1, /* casedn_multiply*/ - 1, /* mbminlen */ - 1, /* mbmaxlen */ - 0, /* min_sort_char */ - 255, /* max_sort_char */ - ' ', /* pad_char */ - 0, /* escape_with_backslash_is_dangerous */ - &my_charset_8bit_handler, - &my_collation_8bit_bin_handler, -} -, -#endif #ifdef HAVE_CHARSET_latin1 { 94,0,0, === modified file 'strings/ctype-sjis.c' --- a/strings/ctype-sjis.c 2007-10-04 07:10:15 +0000 +++ b/strings/ctype-sjis.c 2009-07-02 10:15:33 +0000 @@ -4672,7 +4672,7 @@ static MY_CHARSET_HANDLER my_charset_han CHARSET_INFO my_charset_sjis_japanese_ci= { 13,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM, /* state */ + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_NONASCII, /* state */ "sjis", /* cs name */ "sjis_japanese_ci", /* name */ "", /* comment */ @@ -4704,7 +4704,7 @@ CHARSET_INFO my_charset_sjis_japanese_ci CHARSET_INFO my_charset_sjis_bin= { 88,0,0, /* number */ - MY_CS_COMPILED|MY_CS_BINSORT, /* state */ + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_NONASCII, /* state */ "sjis", /* cs name */ "sjis_bin", /* name */ "", /* comment */ === modified file 'strings/ctype-uca.c' --- a/strings/ctype-uca.c 2007-07-03 09:06:57 +0000 +++ b/strings/ctype-uca.c 2009-07-02 10:15:33 +0000 @@ -8086,7 +8086,7 @@ MY_COLLATION_HANDLER my_collation_ucs2_u CHARSET_INFO my_charset_ucs2_unicode_ci= { 128,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_unicode_ci", /* name */ "", /* comment */ @@ -8118,7 +8118,7 @@ CHARSET_INFO my_charset_ucs2_unicode_ci= CHARSET_INFO my_charset_ucs2_icelandic_uca_ci= { 129,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_icelandic_ci",/* name */ "", /* comment */ @@ -8150,7 +8150,7 @@ CHARSET_INFO my_charset_ucs2_icelandic_u CHARSET_INFO my_charset_ucs2_latvian_uca_ci= { 130,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_latvian_ci", /* name */ "", /* comment */ @@ -8182,7 +8182,7 @@ CHARSET_INFO my_charset_ucs2_latvian_uca CHARSET_INFO my_charset_ucs2_romanian_uca_ci= { 131,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_romanian_ci", /* name */ "", /* comment */ @@ -8214,7 +8214,7 @@ CHARSET_INFO my_charset_ucs2_romanian_uc CHARSET_INFO my_charset_ucs2_slovenian_uca_ci= { 132,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_slovenian_ci",/* name */ "", /* comment */ @@ -8246,7 +8246,7 @@ CHARSET_INFO my_charset_ucs2_slovenian_u CHARSET_INFO my_charset_ucs2_polish_uca_ci= { 133,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_polish_ci", /* name */ "", /* comment */ @@ -8278,7 +8278,7 @@ CHARSET_INFO my_charset_ucs2_polish_uca_ CHARSET_INFO my_charset_ucs2_estonian_uca_ci= { 134,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_estonian_ci", /* name */ "", /* comment */ @@ -8310,7 +8310,7 @@ CHARSET_INFO my_charset_ucs2_estonian_uc CHARSET_INFO my_charset_ucs2_spanish_uca_ci= { 135,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_spanish_ci", /* name */ "", /* comment */ @@ -8342,7 +8342,7 @@ CHARSET_INFO my_charset_ucs2_spanish_uca CHARSET_INFO my_charset_ucs2_swedish_uca_ci= { 136,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_swedish_ci", /* name */ "", /* comment */ @@ -8374,7 +8374,7 @@ CHARSET_INFO my_charset_ucs2_swedish_uca CHARSET_INFO my_charset_ucs2_turkish_uca_ci= { 137,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_turkish_ci", /* name */ "", /* comment */ @@ -8406,7 +8406,7 @@ CHARSET_INFO my_charset_ucs2_turkish_uca CHARSET_INFO my_charset_ucs2_czech_uca_ci= { 138,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_czech_ci", /* name */ "", /* comment */ @@ -8439,7 +8439,7 @@ CHARSET_INFO my_charset_ucs2_czech_uca_c CHARSET_INFO my_charset_ucs2_danish_uca_ci= { 139,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_danish_ci", /* name */ "", /* comment */ @@ -8471,7 +8471,7 @@ CHARSET_INFO my_charset_ucs2_danish_uca_ CHARSET_INFO my_charset_ucs2_lithuanian_uca_ci= { 140,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_lithuanian_ci",/* name */ "", /* comment */ @@ -8503,7 +8503,7 @@ CHARSET_INFO my_charset_ucs2_lithuanian_ CHARSET_INFO my_charset_ucs2_slovak_uca_ci= { 141,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_slovak_ci", /* name */ "", /* comment */ @@ -8535,7 +8535,7 @@ CHARSET_INFO my_charset_ucs2_slovak_uca_ CHARSET_INFO my_charset_ucs2_spanish2_uca_ci= { 142,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_spanish2_ci", /* name */ "", /* comment */ @@ -8568,7 +8568,7 @@ CHARSET_INFO my_charset_ucs2_spanish2_uc CHARSET_INFO my_charset_ucs2_roman_uca_ci= { 143,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_roman_ci", /* name */ "", /* comment */ @@ -8601,7 +8601,7 @@ CHARSET_INFO my_charset_ucs2_roman_uca_c CHARSET_INFO my_charset_ucs2_persian_uca_ci= { 144,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_persian_ci", /* name */ "", /* comment */ @@ -8634,7 +8634,7 @@ CHARSET_INFO my_charset_ucs2_persian_uca CHARSET_INFO my_charset_ucs2_esperanto_uca_ci= { 145,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_esperanto_ci",/* name */ "", /* comment */ @@ -8667,7 +8667,7 @@ CHARSET_INFO my_charset_ucs2_esperanto_u CHARSET_INFO my_charset_ucs2_hungarian_uca_ci= { 146,0,0, /* number */ - MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_hungarian_ci",/* name */ "", /* comment */ === modified file 'strings/ctype-ucs2.c' --- a/strings/ctype-ucs2.c 2009-02-13 16:41:47 +0000 +++ b/strings/ctype-ucs2.c 2009-07-02 10:15:33 +0000 @@ -1717,7 +1717,7 @@ MY_CHARSET_HANDLER my_charset_ucs2_handl CHARSET_INFO my_charset_ucs2_general_ci= { 35,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_general_ci", /* name */ "", /* comment */ @@ -1749,7 +1749,7 @@ CHARSET_INFO my_charset_ucs2_general_ci= CHARSET_INFO my_charset_ucs2_bin= { 90,0,0, /* number */ - MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE, + MY_CS_COMPILED|MY_CS_BINSORT|MY_CS_UNICODE|MY_CS_NONASCII, "ucs2", /* cs name */ "ucs2_bin", /* name */ "", /* comment */ === modified file 'strings/ctype-utf8.c' --- a/strings/ctype-utf8.c 2008-02-11 12:28:33 +0000 +++ b/strings/ctype-utf8.c 2009-07-02 10:15:33 +0000 @@ -4204,7 +4204,7 @@ static MY_CHARSET_HANDLER my_charset_fil CHARSET_INFO my_charset_filename= { 17,0,0, /* number */ - MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_HIDDEN, + MY_CS_COMPILED|MY_CS_PRIMARY|MY_CS_STRNXFRM|MY_CS_UNICODE|MY_CS_HIDDEN|MY_CS_NONASCII, "filename", /* cs name */ "filename", /* name */ "", /* comment */ === modified file 'strings/ctype.c' --- a/strings/ctype.c 2009-04-25 10:05:32 +0000 +++ b/strings/ctype.c 2009-07-02 10:15:33 +0000 @@ -405,3 +405,23 @@ my_charset_is_8bit_pure_ascii(CHARSET_IN } return 1; } + + +/* + Shared function between conf_to_src and mysys. + Check if a 8bit character set is compatible with + ascii on the range 0x00..0x7F. +*/ +my_bool +my_charset_is_ascii_compatible(CHARSET_INFO *cs) +{ + uint i; + if (!cs->tab_to_uni) + return 1; + for (i= 0; i < 128; i++) + { + if (cs->tab_to_uni[i] != i) + return 0; + } + return 1; +}

1 0

[Maria-developers] Updated (by Guest): dynamic versionning of query plan for performance metric and downgrade . (33)
by worklog-noreply＠askmonty.org 01 Jul '09

01 Jul '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: dynamic versionning of query plan for performance metric and downgrade . CREATION DATE..: Tue, 30 Jun 2009, 21:37 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 33 (http://askmonty.org/worklog/?tid=33) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 30 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Guest - Wed, 01 Jul 2009, 00:27)=-=- High Level Description modified. --- /tmp/wklog.33.old.20217 2009-07-01 00:27:24.000000000 +0300 +++ /tmp/wklog.33.new.20217 2009-07-01 00:27:24.000000000 +0300 @@ -1,6 +1,6 @@ Just for comparing apple and oranges , -A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. +A lot of SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. MariaDb could provide dynamic variable QP_vesion = 41|50|51 ... -=-=(Fromdual - Tue, 30 Jun 2009, 21:53)=-=- High Level Description modified. --- /tmp/wklog.33.old.14359 2009-06-30 21:53:31.000000000 +0300 +++ /tmp/wklog.33.new.14359 2009-06-30 21:53:31.000000000 +0300 @@ -3,11 +3,13 @@ A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. -MariaDb should provide dynamique QP and provide a ratio of efficiency in regard -with the number of handler operations, each user would so on, be able to found -out, if an improvement or a bug in the data acess path match is wokload . With -such feature 5.0 to 5.1 would have found an inconsistant ratio of 2 to 1/1000 -with a serious fluctuation on time depending on closing , reopening , reclosing -and reopening bugs like +MariaDb could provide dynamic variable QP_vesion = 41|50|51 ... + +providing benchmarks with a ratio of efficiency in regard with the number of +handler operations, each user would so on, be able to found if an improvement or +a bug in the data acess path match is wokload . +With such feature 5.0 to 5.1 migration would have provide an inconsistant metric +of 2 to 1/1000 with a serious fluctuation on time depending on closing , +reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 -=-=(Fromdual - Tue, 30 Jun 2009, 21:44)=-=- Title modified. --- /tmp/wklog.33.old.13936 2009-06-30 21:44:50.000000000 +0300 +++ /tmp/wklog.33.new.13936 2009-06-30 21:44:50.000000000 +0300 @@ -1 +1 @@ -Dynamique versionning of query plan for performance metric and downgrade . +dynamic versionning of query plan for performance metric and downgrade . DESCRIPTION: Just for comparing apple and oranges , A lot of SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. MariaDb could provide dynamic variable QP_vesion = 41|50|51 ... providing benchmarks with a ratio of efficiency in regard with the number of handler operations, each user would so on, be able to found if an improvement or a bug in the data acess path match is wokload . With such feature 5.0 to 5.1 migration would have provide an inconsistant metric of 2 to 1/1000 with a serious fluctuation on time depending on closing , reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Fromdual): dynamic versionning of query plan for performance metric and downgrade . (33)
by worklog-noreply＠askmonty.org 01 Jul '09

01 Jul '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: dynamic versionning of query plan for performance metric and downgrade . CREATION DATE..: Tue, 30 Jun 2009, 21:37 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 33 (http://askmonty.org/worklog/?tid=33) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 30 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Fromdual - Tue, 30 Jun 2009, 21:53)=-=- High Level Description modified. --- /tmp/wklog.33.old.14359 2009-06-30 21:53:31.000000000 +0300 +++ /tmp/wklog.33.new.14359 2009-06-30 21:53:31.000000000 +0300 @@ -3,11 +3,13 @@ A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. -MariaDb should provide dynamique QP and provide a ratio of efficiency in regard -with the number of handler operations, each user would so on, be able to found -out, if an improvement or a bug in the data acess path match is wokload . With -such feature 5.0 to 5.1 would have found an inconsistant ratio of 2 to 1/1000 -with a serious fluctuation on time depending on closing , reopening , reclosing -and reopening bugs like +MariaDb could provide dynamic variable QP_vesion = 41|50|51 ... + +providing benchmarks with a ratio of efficiency in regard with the number of +handler operations, each user would so on, be able to found if an improvement or +a bug in the data acess path match is wokload . +With such feature 5.0 to 5.1 migration would have provide an inconsistant metric +of 2 to 1/1000 with a serious fluctuation on time depending on closing , +reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 -=-=(Fromdual - Tue, 30 Jun 2009, 21:44)=-=- Title modified. --- /tmp/wklog.33.old.13936 2009-06-30 21:44:50.000000000 +0300 +++ /tmp/wklog.33.new.13936 2009-06-30 21:44:50.000000000 +0300 @@ -1 +1 @@ -Dynamique versionning of query plan for performance metric and downgrade . +dynamic versionning of query plan for performance metric and downgrade . DESCRIPTION: Just for comparing apple and oranges , A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. MariaDb could provide dynamic variable QP_vesion = 41|50|51 ... providing benchmarks with a ratio of efficiency in regard with the number of handler operations, each user would so on, be able to found if an improvement or a bug in the data acess path match is wokload . With such feature 5.0 to 5.1 migration would have provide an inconsistant metric of 2 to 1/1000 with a serious fluctuation on time depending on closing , reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Fromdual): dynamic versionning of query plan for performance metric and downgrade . (33)
by worklog-noreply＠askmonty.org 01 Jul '09

01 Jul '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: dynamic versionning of query plan for performance metric and downgrade . CREATION DATE..: Tue, 30 Jun 2009, 21:37 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 33 (http://askmonty.org/worklog/?tid=33) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 30 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Fromdual - Tue, 30 Jun 2009, 21:44)=-=- Title modified. --- /tmp/wklog.33.old.13936 2009-06-30 21:44:50.000000000 +0300 +++ /tmp/wklog.33.new.13936 2009-06-30 21:44:50.000000000 +0300 @@ -1 +1 @@ -Dynamique versionning of query plan for performance metric and downgrade . +dynamic versionning of query plan for performance metric and downgrade . DESCRIPTION: Just for comparing apple and oranges , A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. MariaDb should provide dynamique QP and provide a ratio of efficiency in regard with the number of handler operations, each user would so on, be able to found out, if an improvement or a bug in the data acess path match is wokload . With such feature 5.0 to 5.1 would have found an inconsistant ratio of 2 to 1/1000 with a serious fluctuation on time depending on closing , reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Fromdual): Dynamique versionning of query plan for performance metric and downgrade . (33)
by worklog-noreply＠askmonty.org 01 Jul '09

01 Jul '09

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Dynamique versionning of query plan for performance metric and downgrade . CREATION DATE..: Tue, 30 Jun 2009, 21:37 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 33 (http://askmonty.org/worklog/?tid=33) VERSION........: WorkLog-3.4 STATUS.........: Un-Assigned PRIORITY.......: 30 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Just for comparing apple and oranges , A lot of internal SUN/ORACLE benchmarks are reporting performance improvements. But they are only tested on specific workload and predefined scenario like DBT2. MariaDb should provide dynamique QP and provide a ratio of efficiency in regard with the number of handler operations, each user would so on, be able to found out, if an improvement or a bug in the data acess path match is wokload . With such feature 5.0 to 5.1 would have found an inconsistant ratio of 2 to 1/1000 with a serious fluctuation on time depending on closing , reopening , reclosing and reopening bugs like http://bugs.mysql.com/bug.php?id=28404 ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Rev 2730: TEst commits 3 in file:///home/psergey/dev/maria-5.1-table-elim-emailcommittests/
by Sergey Petrunya 01 Jul '09

01 Jul '09

At file:///home/psergey/dev/maria-5.1-table-elim-emailcommittests/ ------------------------------------------------------------ revno: 2730 revision-id: psergey(a)askmonty.org-20090630181749-29kxcglcbfaiyygp parent: psergey(a)askmonty.org-20090630180521-32redd6z13g9tluc committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: maria-5.1-table-elim-emailcommittests timestamp: Tue 2009-06-30 22:17:49 +0400 message: TEst commits 3 === modified file 'sql/opt_sum.cc' --- a/sql/opt_sum.cc 2009-04-25 10:05:32 +0000 +++ b/sql/opt_sum.cc 2009-06-30 18:17:49 +0000 @@ -13,7 +13,7 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ - +# error Test commits 3 /** @file

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (psergey:2708)
by Sergey Petrunia 30 Jun '09

30 Jun '09

#At lp:maria based on revid:knielsen@knielsen-hq.org-20090602110359-n4q9gof38buucrny 2708 Sergey Petrunia 2009-06-30 MWL#17: Table elimination - RC0 code added: mysql-test/r/table_elim.result mysql-test/t/table_elim.test sql-bench/test-table-elimination.sh sql/opt_table_elimination.cc modified: libmysqld/Makefile.am mysql-test/r/ps_11bugs.result mysql-test/r/select.result mysql-test/r/subselect.result mysql-test/r/union.result sql/CMakeLists.txt sql/Makefile.am sql/item.cc sql/item.h sql/item_subselect.cc sql/item_subselect.h sql/item_sum.cc sql/item_sum.h sql/sql_lex.cc sql/sql_lex.h sql/sql_select.cc sql/sql_select.h sql/table.h per-file messages: libmysqld/Makefile.am MWL#17: Table elimination - add opt_table_elimination.cc mysql-test/r/ps_11bugs.result MWL#17: Table elimination - Update test results (the difference is because we now recoginze Item_ref(const_item) as const mysql-test/r/select.result MWL#17: Table elimination - Update test results mysql-test/r/subselect.result MWL#17: Table elimination - Update test results (the difference is because we now recoginze Item_ref(const_item) as const mysql-test/r/table_elim.result MWL#17: Table elimination - Testcases mysql-test/r/union.result MWL#17: Table elimination - Update test results (the difference is because we now recoginze Item_ref(const_item) as const mysql-test/t/table_elim.test MWL#17: Table elimination - Testcases sql-bench/test-table-elimination.sh MWL#17: Table elimination - Benchmark which compares table elimination queries with no-table-elimination queries sql/CMakeLists.txt MWL#17: Table elimination - add opt_table_elimination.cc sql/Makefile.am MWL#17: Table elimination - add opt_table_elimination.cc sql/item.cc MWL#17: Table elimination - Add Item_field::check_column_usage_processor sql/item.h MWL#17: Table elimination - Add check_column_usage_processor() sql/item_subselect.cc MWL#17: Table elimination - Make Item_subselect to = be able to tell which particular items are referred from inside the select = to tell whether it was eliminated sql/item_subselect.h MWL#17: Table elimination - Make Item_subselect to = be able to tell which particular items are referred from inside the select = to tell whether it was eliminated sql/item_sum.cc MWL#17: Table elimination - Fix Item_sum_sum::used_tables() to report tables whose columns it really needs sql/item_sum.h MWL#17: Table elimination - Fix Item_sum_sum::used_tables() to report tables whose columns it really needs sql/opt_table_elimination.cc MWL#17: Table elimination - Table elimination Module sql/sql_lex.cc MWL#17: Table elimination - Collect Item_subselect::refers_to attribute sql/sql_lex.h MWL#17: Table elimination - Collect Item_subselect::refers_to attribute sql/sql_select.cc MWL#17: Table elimination - Make KEYUSE array code to also collect/process "binding" equalities in form t.keyXpartY= func(t.keyXpartZ,...) - Call table elimination function - Make EXPLAIN not to show eliminated tables/selects - Added more comments - Move definitions of FT_KEYPART, KEY_OPTIMIZE_* into sql_select.h as they are now used in opt_table_elimination.cc sql/sql_select.h MWL#17: Table elimination - Make KEYUSE array code to also collect/process "binding" equalities in form t.keyXpartY= func(t.keyXpartZ,...) - Call table elimination function - Make EXPLAIN not to show eliminated tables/selects - Added more comments - Move definitions of FT_KEYPART, KEY_OPTIMIZE_* into sql_select.h as they are now used in opt_table_elimination.cc sql/table.h MWL#17: Table elimination - More comments - Add NESTED_JOIN::n_tables === modified file 'libmysqld/Makefile.am' --- a/libmysqld/Makefile.am 2009-03-12 22:27:35 +0000 +++ b/libmysqld/Makefile.am 2009-06-30 15:09:36 +0000 @@ -76,7 +76,7 @@ sqlsources = derror.cc field.cc field_co rpl_filter.cc sql_partition.cc sql_builtin.cc sql_plugin.cc \ sql_tablespace.cc \ rpl_injector.cc my_user.c partition_info.cc \ - sql_servers.cc event_parse_data.cc + sql_servers.cc event_parse_data.cc opt_table_elimination.cc libmysqld_int_a_SOURCES= $(libmysqld_sources) nodist_libmysqld_int_a_SOURCES= $(libmysqlsources) $(sqlsources) === modified file 'mysql-test/r/ps_11bugs.result' --- a/mysql-test/r/ps_11bugs.result 2008-10-08 11:23:53 +0000 +++ b/mysql-test/r/ps_11bugs.result 2009-06-30 15:09:36 +0000 @@ -121,8 +121,8 @@ insert into t1 values (1); explain select * from t1 where 3 in (select (1+1) union select 1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used -3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible HAVING +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL Impossible HAVING NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL select * from t1 where 3 in (select (1+1) union select 1); a === modified file 'mysql-test/r/select.result' --- a/mysql-test/r/select.result 2009-03-16 05:02:10 +0000 +++ b/mysql-test/r/select.result 2009-06-30 15:09:36 +0000 @@ -3585,7 +3585,6 @@ INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3 EXPLAIN SELECT t1.a FROM t1 LEFT JOIN t2 ON t2.b=t1.b WHERE t1.a=3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 -1 SIMPLE t2 const b b 22 const 1 Using index DROP TABLE t1,t2; CREATE TABLE t1(id int PRIMARY KEY, b int, e int); CREATE TABLE t2(i int, a int, INDEX si(i), INDEX ai(a)); === modified file 'mysql-test/r/subselect.result' --- a/mysql-test/r/subselect.result 2009-04-25 09:04:38 +0000 +++ b/mysql-test/r/subselect.result 2009-06-30 15:09:36 +0000 @@ -4353,13 +4353,13 @@ id select_type table type possible_keys 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` group by `test`.`t1`.`a` having 1)) EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY a); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort Warnings: -Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) = <ref_null_helper>(1)))) +Note 1003 select 1 AS `1` from `test`.`t1` where <in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having 1)) DROP TABLE t1; End of 5.0 tests. CREATE TABLE t1 (a INT, b INT); === added file 'mysql-test/r/table_elim.result' --- a/mysql-test/r/table_elim.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/r/table_elim.result 2009-06-30 15:09:36 +0000 @@ -0,0 +1,204 @@ +drop table if exists t0, t1, t2, t3; +drop view if exists v1, v2; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t0 as select * from t1; +create table t2 (a int primary key, b int) +as select a, a as b from t1 where a in (1,2); +create table t3 (a int primary key, b int) +as select a, a as b from t1 where a in (1,3); +# This will be eliminated: +explain select t1.a from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain extended select t1.a from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 +select t1.a from t1 left join t2 on t2.a=t1.a; +a +0 +1 +2 +3 +# This will not be eliminated as t2.b is in in select list: +explain select * from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in in order list: +explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in group list: +explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 +# This will not be eliminated as t2.b is in the WHERE +explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using where +# Elimination of multiple tables: +explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +# Elimination of multiple tables (2): +explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +# Elimination when done within an outer join nest: +explain extended +select t0.* +from +t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and +t3.a=t1.a) on t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 +Warnings: +Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t0`.`a` = `test`.`t1`.`a`)) where 1 +# Elimination with aggregate functions +explain select count(*) from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select count(1) from t1 left join t2 on t2.a=t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +This must not use elimination: +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary; Using filesort +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index +drop table t0, t1, t2, t3; +create table t0 ( id integer, primary key (id)); +create table t1 ( +id integer, +attr1 integer, +primary key (id), +key (attr1) +); +create table t2 ( +id integer, +attr2 integer, +fromdate date, +primary key (id, fromdate), +key (attr2,fromdate) +); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0; +insert into t1 select id, id from t0; +insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0; +insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0; +create view v1 as +select +F.id, A1.attr1, A2.attr2 +from +t0 F +left join t1 A1 on A1.id=F.id +left join t2 A2 on A2.id=F.id and +A2.fromdate=(select MAX(fromdate) from +t2 where id=A2.id); +create view v2 as +select +F.id, A1.attr1, A2.attr2 +from +t0 F +left join t1 A1 on A1.id=F.id +left join t2 A2 on A2.id=F.id and +A2.fromdate=(select MAX(fromdate) from +t2 where id=F.id); +This should use one table: +explain select id from v1 where id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index +This should use one table: +explain extended select id from v1 where id in (1,2,3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4)) +This should use facts and A1 tables: +explain extended select id from v1 where attr1 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14)) +This should use facts, A2 and its subquery: +explain extended select id from v1 where attr2 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using index +3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.A2.id 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.A2.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `A2`.`id`)))) +This should use one table: +explain select id from v2 where id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY F const PRIMARY PRIMARY 4 const 1 Using index +This should use one table: +explain extended select id from v2 where id in (1,2,3,4); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY F range PRIMARY PRIMARY 4 NULL 4 100.00 Using where; Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` where (`F`.`id` in (1,2,3,4)) +This should use facts and A1 tables: +explain extended select id from v2 where attr1 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A1 range PRIMARY,attr1 attr1 5 NULL 2 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A1.id 1 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t1` `A1` where ((`F`.`id` = `A1`.`id`) and (`A1`.`attr1` between 12 and 14)) +This should use facts, A2 and its subquery: +explain extended select id from v2 where attr2 between 12 and 14; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY A2 range PRIMARY,attr2 attr2 5 NULL 5 100.00 Using where +1 PRIMARY F eq_ref PRIMARY PRIMARY 4 test.A2.id 1 100.00 Using where; Using index +3 DEPENDENT SUBQUERY t2 ref PRIMARY PRIMARY 4 test.F.id 2 100.00 Using index +Warnings: +Note 1276 Field or reference 'test.F.id' of SELECT #3 was resolved in SELECT #1 +Note 1003 select `F`.`id` AS `id` from `test`.`t0` `F` join `test`.`t2` `A2` where ((`F`.`id` = `A2`.`id`) and (`A2`.`attr2` between 12 and 14) and (`A2`.`fromdate` = (select max(`test`.`t2`.`fromdate`) AS `MAX(fromdate)` from `test`.`t2` where (`test`.`t2`.`id` = `F`.`id`)))) +drop view v1, v2; +drop table t0, t1, t2; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3)); +insert into t2 select a,a,a,a from t1; +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk2=t2.pk1+1 and +t2.pk3=t2.pk2+1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk3=t2.pk1+1 and +t2.pk2=t2.pk3+1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +This must use both: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and +t2.pk3=t2.pk1+1 and +t2.pk2=t2.pk3+t2.col; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 test.t1.a 1 +This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk2=t1.a and +t2.pk1=t2.pk2+1 and +t2.pk3=t2.pk1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 +drop table t1, t2; === modified file 'mysql-test/r/union.result' --- a/mysql-test/r/union.result 2009-03-19 10:18:52 +0000 +++ b/mysql-test/r/union.result 2009-06-30 15:09:36 +0000 @@ -522,7 +522,7 @@ id select_type table type possible_keys 2 UNION t2 const PRIMARY PRIMARY 4 const 1 100.00 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where ('1' = 1)) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where ('1' = 1)) +Note 1003 (select '1' AS `a`,'1' AS `b` from `test`.`t1` where 1) union (select '1' AS `a`,'10' AS `b` from `test`.`t2` where 1) (select * from t1 where a=5) union (select * from t2 where a=1); a b 1 10 === added file 'mysql-test/t/table_elim.test' --- a/mysql-test/t/table_elim.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/t/table_elim.test 2009-06-30 15:09:36 +0000 @@ -0,0 +1,160 @@ +# +# Table elimination (MWL#17) tests +# +--disable_warnings +drop table if exists t0, t1, t2, t3; +drop view if exists v1, v2; +--enable_warnings + +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); +create table t0 as select * from t1; + +create table t2 (a int primary key, b int) + as select a, a as b from t1 where a in (1,2); + +create table t3 (a int primary key, b int) + as select a, a as b from t1 where a in (1,3); + +--echo # This will be eliminated: +explain select t1.a from t1 left join t2 on t2.a=t1.a; +explain extended select t1.a from t1 left join t2 on t2.a=t1.a; + +select t1.a from t1 left join t2 on t2.a=t1.a; + +--echo # This will not be eliminated as t2.b is in in select list: +explain select * from t1 left join t2 on t2.a=t1.a; + +--echo # This will not be eliminated as t2.b is in in order list: +explain select t1.a from t1 left join t2 on t2.a=t1.a order by t2.b; + +--echo # This will not be eliminated as t2.b is in group list: +explain select t1.a from t1 left join t2 on t2.a=t1.a group by t2.b; + +--echo # This will not be eliminated as t2.b is in the WHERE +explain select t1.a from t1 left join t2 on t2.a=t1.a where t2.b < 3 or t2.b is null; + +--echo # Elimination of multiple tables: +explain select t1.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a; + +--echo # Elimination of multiple tables (2): +explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and t3.a=t1.a; + +--echo # Elimination when done within an outer join nest: +explain extended +select t0.* +from + t0 left join (t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and + t3.a=t1.a) on t0.a=t1.a; + +--echo # Elimination with aggregate functions +explain select count(*) from t1 left join t2 on t2.a=t1.a; +explain select count(1) from t1 left join t2 on t2.a=t1.a; +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t1.a; + +--echo This must not use elimination: +explain select count(1) from t1 left join t2 on t2.a=t1.a group by t2.a; + +drop table t0, t1, t2, t3; + +# This will stand for elim_facts +create table t0 ( id integer, primary key (id)); + +# Attribute1, non-versioned +create table t1 ( + id integer, + attr1 integer, + primary key (id), + key (attr1) +); + +# Attribute2, time-versioned +create table t2 ( + id integer, + attr2 integer, + fromdate date, + primary key (id, fromdate), + key (attr2,fromdate) +); + +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t0 select A.id + 10*B.id from t0 A, t0 B where B.id > 0; + +insert into t1 select id, id from t0; +insert into t2 select id, id, date_add('2009-06-22', interval id day) from t0; +insert into t2 select id, id+1, date_add('2008-06-22', interval id day) from t0; + +create view v1 as +select + F.id, A1.attr1, A2.attr2 +from + t0 F + left join t1 A1 on A1.id=F.id + left join t2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + t2 where id=A2.id); +create view v2 as +select + F.id, A1.attr1, A2.attr2 +from + t0 F + left join t1 A1 on A1.id=F.id + left join t2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + t2 where id=F.id); + +--echo This should use one table: +explain select id from v1 where id=2; +--echo This should use one table: +explain extended select id from v1 where id in (1,2,3,4); +--echo This should use facts and A1 tables: +explain extended select id from v1 where attr1 between 12 and 14; +--echo This should use facts, A2 and its subquery: +explain extended select id from v1 where attr2 between 12 and 14; + +# Repeat for v2: + +--echo This should use one table: +explain select id from v2 where id=2; +--echo This should use one table: +explain extended select id from v2 where id in (1,2,3,4); +--echo This should use facts and A1 tables: +explain extended select id from v2 where attr1 between 12 and 14; +--echo This should use facts, A2 and its subquery: +explain extended select id from v2 where attr2 between 12 and 14; + +drop view v1, v2; +drop table t0, t1, t2; + +# +# Tests for the code that uses t.keypartX=func(t.keypartY) equalities to +# make table elimination inferences +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3); + +create table t2 (pk1 int, pk2 int, pk3 int, col int, primary key(pk1, pk2, pk3)); +insert into t2 select a,a,a,a from t1; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk2=t2.pk1+1 and + t2.pk3=t2.pk2+1; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk3=t2.pk1+1 and + t2.pk2=t2.pk3+1; + +--echo This must use both: +explain select t1.* from t1 left join t2 on t2.pk1=t1.a and + t2.pk3=t2.pk1+1 and + t2.pk2=t2.pk3+t2.col; + +--echo This must use only t1: +explain select t1.* from t1 left join t2 on t2.pk2=t1.a and + t2.pk1=t2.pk2+1 and + t2.pk3=t2.pk1; + +drop table t1, t2; + === added file 'sql-bench/test-table-elimination.sh' --- a/sql-bench/test-table-elimination.sh 1970-01-01 00:00:00 +0000 +++ b/sql-bench/test-table-elimination.sh 2009-06-30 15:09:36 +0000 @@ -0,0 +1,320 @@ +#!@PERL@ +# Test of table elimination feature + +use Cwd; +use DBI; +use Getopt::Long; +use Benchmark; + +$opt_loop_count=100000; +$opt_medium_loop_count=10000; +$opt_small_loop_count=100; + +$pwd = cwd(); $pwd = "." if ($pwd eq ''); +require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n"; + +if ($opt_small_test) +{ + $opt_loop_count/=10; + $opt_medium_loop_count/=10; + $opt_small_loop_count/=10; +} + +print "Testing table elimination feature\n"; +print "The test table has $opt_loop_count rows.\n\n"; + +# A query to get the recent versions of all attributes: +$select_current_full_facts=" + select + F.id, A1.attr1, A2.attr2 + from + elim_facts F + left join elim_attr1 A1 on A1.id=F.id + left join elim_attr2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + elim_attr2 where id=A2.id); +"; +$select_current_full_facts=" + select + F.id, A1.attr1, A2.attr2 + from + elim_facts F + left join elim_attr1 A1 on A1.id=F.id + left join elim_attr2 A2 on A2.id=F.id and + A2.fromdate=(select MAX(fromdate) from + elim_attr2 where id=F.id); +"; +# TODO: same as above but for some given date also? +# TODO: + + +#### +#### Connect and start timeing +#### + +$dbh = $server->connect(); +$start_time=new Benchmark; + +#### +#### Create needed tables +#### + +goto select_test if ($opt_skip_create); + +print "Creating tables\n"; +$dbh->do("drop table elim_facts" . $server->{'drop_attr'}); +$dbh->do("drop table elim_attr1" . $server->{'drop_attr'}); +$dbh->do("drop table elim_attr2" . $server->{'drop_attr'}); + +# The facts table +do_many($dbh,$server->create("elim_facts", + ["id integer"], + ["primary key (id)"])); + +# Attribute1, non-versioned +do_many($dbh,$server->create("elim_attr1", + ["id integer", + "attr1 integer"], + ["primary key (id)", + "key (attr1)"])); + +# Attribute2, time-versioned +do_many($dbh,$server->create("elim_attr2", + ["id integer", + "attr2 integer", + "fromdate date"], + ["primary key (id, fromdate)", + "key (attr2,fromdate)"])); + +#NOTE: ignoring: if ($limits->{'views'}) +$dbh->do("drop view elim_current_facts"); +$dbh->do("create view elim_current_facts as $select_current_full_facts"); + +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES elim_facts, elim_attr1, elim_attr2 WRITE"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(1,\$dbh); +} + +#### +#### Fill the facts table +#### +$n_facts= $opt_loop_count; + +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + +print "Inserting $n_facts rows into facts table\n"; +$loop_time=new Benchmark; + +$query="insert into elim_facts values ("; +for ($id=0; $id < $n_facts ; $id++) +{ + do_query($dbh,"$query $id)"); +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + +$end_time=new Benchmark; +print "Time to insert ($n_facts): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Fill attr1 table +#### +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + +print "Inserting $n_facts rows into attr1 table\n"; +$loop_time=new Benchmark; + +$query="insert into elim_attr1 values ("; +for ($id=0; $id < $n_facts ; $id++) +{ + $attr1= ceil(rand($n_facts)); + do_query($dbh,"$query $id, $attr1)"); +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + +$end_time=new Benchmark; +print "Time to insert ($n_facts): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Fill attr2 table +#### +if ($opt_fast && $server->{transactions}) +{ + $dbh->{AutoCommit} = 0; +} + +print "Inserting $n_facts rows into attr2 table\n"; +$loop_time=new Benchmark; + +for ($id=0; $id < $n_facts ; $id++) +{ + # Two values for each $id - current one and obsolete one. + $attr1= ceil(rand($n_facts)); + $query="insert into elim_attr2 values ($id, $attr1, now())"; + do_query($dbh,$query); + $query="insert into elim_attr2 values ($id, $attr1, '2009-01-01')"; + do_query($dbh,$query); +} + +if ($opt_fast && $server->{transactions}) +{ + $dbh->commit; + $dbh->{AutoCommit} = 1; +} + +$end_time=new Benchmark; +print "Time to insert ($n_facts): " . + timestr(timediff($end_time, $loop_time),"all") . "\n\n"; + +#### +#### Finalize the database population +#### + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh,["elim_facts", "elim_attr1", "elim_attr2"]); +} + +if ($opt_lock_tables) +{ + do_query($dbh,"LOCK TABLES elim_facts, elim_attr1, elim_attr2 WRITE"); +} + +#### +#### Do some selects on the table +#### + +select_test: + +# +# The selects will be: +# - N pk-lookups with all attributes +# - pk-attribute-based lookup +# - latest-attribute value based lookup. + + +### +### Bare facts select: +### +print "testing bare facts facts table\n"; +$loop_time=new Benchmark; +$rows=0; +for ($i=0 ; $i < $opt_medium_loop_count ; $i++) +{ + $val= ceil(rand($n_facts)); + $rows+=fetch_all_rows($dbh,"select * from elim_facts where id=$val"); +} +$count=$i; + +$end_time=new Benchmark; +print "time for select_bare_facts ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + +### +### Full facts select, no elimination: +### +print "testing full facts facts table\n"; +$loop_time=new Benchmark; +$rows=0; +for ($i=0 ; $i < $opt_medium_loop_count ; $i++) +{ + $val= rand($n_facts); + $rows+=fetch_all_rows($dbh,"select * from elim_current_facts where id=$val"); +} +$count=$i; + +$end_time=new Benchmark; +print "time for select_two_attributes ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +### +### Now with elimination: select only only one fact +### +print "testing selection of one attribute\n"; +$loop_time=new Benchmark; +$rows=0; +for ($i=0 ; $i < $opt_medium_loop_count ; $i++) +{ + $val= rand($n_facts); + $rows+=fetch_all_rows($dbh,"select id, attr1 from elim_current_facts where id=$val"); +} +$count=$i; + +$end_time=new Benchmark; +print "time for select_one_attribute ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + +### +### Now with elimination: select only only one fact +### +print "testing selection of one attribute\n"; +$loop_time=new Benchmark; +$rows=0; +for ($i=0 ; $i < $opt_medium_loop_count ; $i++) +{ + $val= rand($n_facts); + $rows+=fetch_all_rows($dbh,"select id, attr2 from elim_current_facts where id=$val"); +} +$count=$i; + +$end_time=new Benchmark; +print "time for select_one_attribute ($count:$rows): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + + +### +### TODO... +### + +; + +#### +#### End of benchmark +#### + +if ($opt_lock_tables) +{ + do_query($dbh,"UNLOCK TABLES"); +} +if (!$opt_skip_delete) +{ + do_query($dbh,"drop table elim_facts, elim_attr1, elim_attr2" . $server->{'drop_attr'}); +} + +if ($opt_fast && defined($server->{vacuum})) +{ + $server->vacuum(0,\$dbh); +} + +$dbh->disconnect; # close connection + +end_benchmark($start_time); + === modified file 'sql/CMakeLists.txt' --- a/sql/CMakeLists.txt 2008-11-21 14:21:50 +0000 +++ b/sql/CMakeLists.txt 2009-06-30 15:09:36 +0000 @@ -73,7 +73,7 @@ ADD_EXECUTABLE(mysqld partition_info.cc rpl_utility.cc rpl_injector.cc sql_locale.cc rpl_rli.cc rpl_mi.cc sql_servers.cc sql_connect.cc scheduler.cc - sql_profile.cc event_parse_data.cc + sql_profile.cc event_parse_data.cc opt_table_elimination.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h ${PROJECT_SOURCE_DIR}/include/mysqld_error.h === modified file 'sql/Makefile.am' --- a/sql/Makefile.am 2009-03-12 22:27:35 +0000 +++ b/sql/Makefile.am 2009-06-30 15:09:36 +0000 @@ -121,7 +121,8 @@ mysqld_SOURCES = sql_lex.cc sql_handler. event_queue.cc event_db_repository.cc events.cc \ sql_plugin.cc sql_binlog.cc \ sql_builtin.cc sql_tablespace.cc partition_info.cc \ - sql_servers.cc event_parse_data.cc + sql_servers.cc event_parse_data.cc \ + opt_table_elimination.cc nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c === modified file 'sql/item.cc' --- a/sql/item.cc 2009-04-25 10:05:32 +0000 +++ b/sql/item.cc 2009-06-30 15:09:36 +0000 @@ -1915,6 +1915,37 @@ void Item_field::reset_field(Field *f) name= (char*) f->field_name; } + +bool Item_field::check_column_usage_processor(uchar *arg) +{ + Field_processor_info* info=(Field_processor_info*)arg; + + if (field->table == info->table) + { + /* It is not ok to use columns that are not part of the key of interest: */ + if (!(field->part_of_key.is_set(info->keyno))) + return TRUE; + + /* Find which key part we're using and mark it in needed_key_parts */ + KEY *key= &field->table->key_info[info->keyno]; + for (uint part= 0; part < key->key_parts; part++) + { + if (field->field_index == key->key_part[part].field->field_index) + { + if (part == info->forbidden_part) + return TRUE; + info->needed_key_parts |= key_part_map(1) << part; + break; + } + } + return FALSE; + } + else + info->used_tables |= this->used_tables(); + return FALSE; +} + + const char *Item_ident::full_name() const { char *tmp; @@ -3380,7 +3411,7 @@ static void mark_as_dependent(THD *thd, /* store pointer on SELECT_LEX from which item is dependent */ if (mark_item) mark_item->depended_from= last; - current->mark_as_dependent(last); + current->mark_as_dependent(last, resolved_item); if (thd->lex->describe & DESCRIBE_EXTENDED) { char warn_buff[MYSQL_ERRMSG_SIZE]; === modified file 'sql/item.h' --- a/sql/item.h 2009-04-25 10:05:32 +0000 +++ b/sql/item.h 2009-06-30 15:09:36 +0000 @@ -731,7 +731,11 @@ public: virtual bool val_bool_result() { return val_bool(); } virtual bool is_null_result() { return is_null(); } - /* bit map of tables used by item */ + /* + Bitmap of tables used by item + (note: if you need to check dependencies on individual columns, check out + check_column_usage_processor) + */ virtual table_map used_tables() const { return (table_map) 0L; } /* Return table map of tables that can't be NULL tables (tables that are @@ -888,6 +892,8 @@ public: virtual bool reset_query_id_processor(uchar *query_id_arg) { return 0; } virtual bool is_expensive_processor(uchar *arg) { return 0; } virtual bool register_field_in_read_map(uchar *arg) { return 0; } + virtual bool check_column_usage_processor(uchar *arg) { return 0; } + virtual bool mark_as_eliminated_processor(uchar *arg) { return 0; } /* Check if a partition function is allowed SYNOPSIS @@ -1011,6 +1017,18 @@ public: bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); }; +/* Data for Item::check_column_usage_processor */ +typedef struct +{ + TABLE *table; /* Table of interest */ + uint keyno; /* Index of interest */ + uint forbidden_part; /* key part which one is not allowed to refer to */ + /* [Set by processor] used tables, besides the table of interest */ + table_map used_tables; + /* [Set by processor] Parts of index of interest that expression refers to */ + uint needed_key_parts; +} Field_processor_info; + class sp_head; @@ -1477,6 +1495,7 @@ public: bool find_item_in_field_list_processor(uchar *arg); bool register_field_in_read_map(uchar *arg); bool check_partition_func_processor(uchar *int_arg) {return FALSE;} + bool check_column_usage_processor(uchar *arg); void cleanup(); bool result_as_longlong() { @@ -2203,6 +2222,10 @@ public: if (!depended_from) (*ref)->update_used_tables(); } + bool const_item() const + { + return (*ref)->const_item(); + } table_map not_null_tables() const { return (*ref)->not_null_tables(); } void set_result_field(Field *field) { result_field= field; } bool is_result_field() { return 1; } === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2009-01-31 21:22:44 +0000 +++ b/sql/item_subselect.cc 2009-06-30 15:09:36 +0000 @@ -39,7 +39,7 @@ inline Item * and_items(Item* cond, Item Item_subselect::Item_subselect(): Item_result_field(), value_assigned(0), thd(0), substitution(0), engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0), - const_item_cache(1), engine_changed(0), changed(0), is_correlated(FALSE) + const_item_cache(1), in_fix_fields(0), engine_changed(0), changed(0), is_correlated(FALSE) { with_subselect= 1; reset(); @@ -151,10 +151,14 @@ bool Item_subselect::fix_fields(THD *thd DBUG_ASSERT(fixed == 0); engine->set_thd((thd= thd_param)); + if (!in_fix_fields) + refers_to.empty(); + eliminated= FALSE; if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res)) return TRUE; - + + in_fix_fields++; res= engine->prepare(); // all transformation is done (used by prepared statements) @@ -181,12 +185,14 @@ bool Item_subselect::fix_fields(THD *thd if (!(*ref)->fixed) ret= (*ref)->fix_fields(thd, ref); thd->where= save_where; + in_fix_fields--; return ret; } // Is it one field subselect? if (engine->cols() > max_columns) { my_error(ER_OPERAND_COLUMNS, MYF(0), 1); + in_fix_fields--; return TRUE; } fix_length_and_dec(); @@ -203,11 +209,30 @@ bool Item_subselect::fix_fields(THD *thd fixed= 1; err: + in_fix_fields--; thd->where= save_where; return res; } +bool Item_subselect::check_column_usage_processor(uchar *arg) +{ + List_iterator<Item> it(refers_to); + Item *item; + while ((item= it++)) + { + if (item->walk(&Item::check_column_usage_processor,FALSE, arg)) + return TRUE; + } + return FALSE; +} + +bool Item_subselect::mark_as_eliminated_processor(uchar *arg) +{ + eliminated= TRUE; + return FALSE; +} + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, uchar *argument) { @@ -225,6 +250,7 @@ bool Item_subselect::walk(Item_processor if (lex->having && (lex->having)->walk(processor, walk_subquery, argument)) return 1; + /* TODO: why does this walk WHERE/HAVING but not ON expressions of outer joins? */ while ((item=li++)) { === modified file 'sql/item_subselect.h' --- a/sql/item_subselect.h 2008-02-22 10:30:33 +0000 +++ b/sql/item_subselect.h 2009-06-30 15:09:36 +0000 @@ -52,8 +52,16 @@ protected: bool have_to_be_excluded; /* cache of constant state */ bool const_item_cache; - + public: + /* + References from inside the subquery to the select that this predicate is + in. References to parent selects not included. + */ + List<Item> refers_to; + int in_fix_fields; + bool eliminated; + /* changed engine indicator */ bool engine_changed; /* subquery is transformed */ @@ -126,6 +134,8 @@ public: virtual void reset_value_registration() {} enum_parsing_place place() { return parsing_place; } bool walk(Item_processor processor, bool walk_subquery, uchar *arg); + bool mark_as_eliminated_processor(uchar *arg); + bool check_column_usage_processor(uchar *arg); /** Get the SELECT_LEX structure associated with this Item. === modified file 'sql/item_sum.cc' --- a/sql/item_sum.cc 2009-04-25 09:04:38 +0000 +++ b/sql/item_sum.cc 2009-06-30 15:09:36 +0000 @@ -350,7 +350,7 @@ bool Item_sum::register_sum_func(THD *th sl= sl->master_unit()->outer_select() ) sl->master_unit()->item->with_sum_func= 1; } - thd->lex->current_select->mark_as_dependent(aggr_sel); + thd->lex->current_select->mark_as_dependent(aggr_sel, NULL); return FALSE; } @@ -542,11 +542,6 @@ void Item_sum::update_used_tables () args[i]->update_used_tables(); used_tables_cache|= args[i]->used_tables(); } - - used_tables_cache&= PSEUDO_TABLE_BITS; - - /* the aggregate function is aggregated into its local context */ - used_tables_cache |= (1 << aggr_sel->join->tables) - 1; } } === modified file 'sql/item_sum.h' --- a/sql/item_sum.h 2008-12-09 19:43:10 +0000 +++ b/sql/item_sum.h 2009-06-30 15:09:36 +0000 @@ -255,6 +255,12 @@ protected: */ Item **orig_args, *tmp_orig_args[2]; table_map used_tables_cache; + + /* + TRUE <=> We've managed to calculate the value of this Item in + opt_sum_query(), hence it can be considered constant at all subsequent + steps. + */ bool forced_const; public: @@ -341,6 +347,15 @@ public: virtual const char *func_name() const= 0; virtual Item *result_item(Field *field) { return new Item_field(field); } + /* + Return bitmap of tables that are needed to evaluate the item. + + The implementation takes into account the used strategy: items resolved + at optimization phase will report 0. + Items that depend on the number of join output records, but not columns + of any particular table (like COUNT(*)) will report 0 from used_tables(), + but will still return false from const_item(). + */ table_map used_tables() const { return used_tables_cache; } void update_used_tables (); void cleanup() === added file 'sql/opt_table_elimination.cc' --- a/sql/opt_table_elimination.cc 1970-01-01 00:00:00 +0000 +++ b/sql/opt_table_elimination.cc 2009-06-30 15:09:36 +0000 @@ -0,0 +1,494 @@ +/** + @file + + @brief + Table Elimination Module + + @defgroup Table_Elimination Table Elimination Module + @{ +*/ + +#ifdef USE_PRAGMA_IMPLEMENTATION +#pragma implementation // gcc: Class implementation +#endif + +#include "mysql_priv.h" +#include "sql_select.h" + +/* + OVERVIEW + + The module has one entry point - eliminate_tables() function, which one + needs to call (once) sometime after update_ref_and_keys() but before the + join optimization. + eliminate_tables() operates over the JOIN structures. Logically, it + removes the right sides of outer join nests. Physically, it changes the + following members: + + * Eliminated tables are marked as constant and moved to the front of the + join order. + * In addition to this, they are recorded in JOIN::eliminated_tables bitmap. + + * All join nests have their NESTED_JOIN::n_tables updated to discount + the eliminated tables + + * Items that became disused because they were in the ON expression of an + eliminated outer join are notified by means of the Item tree walk which + calls Item::mark_as_eliminated_processor for every item + - At the moment the only Item that cares is Item_subselect with its + Item_subselect::eliminated flag which is used by EXPLAIN code to + check if the subquery should be shown in EXPLAIN. + + Table elimination is redone on every PS re-execution. +*/ + +static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl); +static bool table_has_one_match(TABLE *table, table_map bound_tables, + bool *multiple_matches); +static uint +eliminate_tables_for_list(JOIN *join, TABLE **leaves_arr, + List<TABLE_LIST> *join_list, + bool its_outer_join, + table_map tables_in_list, + table_map tables_used_elsewhere, + bool *multiple_matches); +static bool +extra_keyuses_bind_all_keyparts(table_map bound_tables, TABLE *table, + KEYUSE *key_start, KEYUSE *key_end, + uint n_keyuses, table_map bound_parts); + +/* + Perform table elimination + + SYNOPSIS + eliminate_tables() + join Join to work on + const_tbl_count INOUT Number of constant tables (this includes + eliminated tables) + const_tables INOUT Bitmap of constant tables + + DESCRIPTION + This function is the entry point for table elimination. + The idea behind table elimination is that if we have an outer join: + + SELECT * FROM t1 LEFT JOIN + (t2 JOIN t3) ON t3.primary_key=t1.col AND + t4.primary_key=t2.col + such that + + 1. columns of the inner tables are not used anywhere ouside the outer + join (not in WHERE, not in GROUP/ORDER BY clause, not in select list + etc etc), and + 2. inner side of the outer join is guaranteed to produce at most one + record combination for each record combination of outer tables. + + then the inner side of the outer join can be removed from the query. + This is because it will always produce one matching record (either a + real match or a NULL-complemented record combination), and since there + are no references to columns of the inner tables anywhere, it doesn't + matter which record combination it was. + + This function primary handles checking #1. It collects a bitmap of + tables that are not used in select list/GROUP BY/ORDER BY/HAVING/etc and + thus can possibly be eliminated. + + SIDE EFFECTS + See the OVERVIEW section at the top of this file. + +*/ + +void eliminate_tables(JOIN *join) +{ + Item *item; + table_map used_tables; + DBUG_ENTER("eliminate_tables"); + + DBUG_ASSERT(join->eliminated_tables == 0); + + /* If there are no outer joins, we have nothing to eliminate: */ + if (!join->outer_join) + DBUG_VOID_RETURN; + + /* Find the tables that are referred to from WHERE/HAVING */ + used_tables= (join->conds? join->conds->used_tables() : 0) | + (join->having? join->having->used_tables() : 0); + + /* Add tables referred to from the select list */ + List_iterator<Item> it(join->fields_list); + while ((item= it++)) + used_tables |= item->used_tables(); + + /* Add tables referred to from ORDER BY and GROUP BY lists */ + ORDER *all_lists[]= { join->order, join->group_list}; + for (int i=0; i < 2; i++) + { + for (ORDER *cur_list= all_lists[i]; cur_list; cur_list= cur_list->next) + used_tables |= (*(cur_list->item))->used_tables(); + } + + THD* thd= join->thd; + if (join->select_lex == &thd->lex->select_lex) + { + /* Multi-table UPDATE and DELETE: don't eliminate the tables we modify: */ + used_tables |= thd->table_map_for_update; + + /* Multi-table UPDATE: don't eliminate tables referred from SET statement */ + if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI) + { + List_iterator<Item> it2(thd->lex->value_list); + while ((item= it2++)) + used_tables |= item->used_tables(); + } + } + + table_map all_tables= join->all_tables_map(); + if (all_tables & ~used_tables) + { + /* There are some tables that we probably could eliminate. Try it. */ + TABLE *leaves_array[MAX_TABLES]; + bool multiple_matches= FALSE; + eliminate_tables_for_list(join, leaves_array, join->join_list, FALSE, + all_tables, used_tables, &multiple_matches); + } + DBUG_VOID_RETURN; +} + +/* + Perform table elimination in a given join list + + SYNOPSIS + eliminate_tables_for_list() + join The join + leaves_arr OUT Store here an array of leaf (base) tables that + are descendants of the join_list, and increment + the pointer to point right above the array. + join_list Join list to work on + its_outer_join TRUE <=> join_list is an inner side of an outer + join + FALSE <=> otherwise (this is top-level join list) + tables_in_list Bitmap of tables embedded in the join_list. + tables_used_elsewhere Bitmap of tables that are referred to from + somewhere outside of the join list (e.g. + select list, HAVING, etc). + + DESCRIPTION + Perform table elimination for a join list. + Try eliminating children nests first. + The "all tables in join nest can produce only one matching record + combination" property checking is modeled after constant table detection, + plus we reuse info attempts to eliminate child join nests. + + RETURN + Number of children left after elimination. 0 means everything was + eliminated. +*/ +static uint +eliminate_tables_for_list(JOIN *join, TABLE **leaves_arr, + List<TABLE_LIST> *join_list, + bool its_outer_join, + table_map tables_in_list, + table_map tables_used_elsewhere, + bool *multiple_matches) +{ + TABLE_LIST *tbl; + List_iterator<TABLE_LIST> it(*join_list); + table_map tables_used_on_left= 0; + TABLE **cur_table= leaves_arr; + bool children_have_multiple_matches= FALSE; + uint remaining_children= 0; + + while ((tbl= it++)) + { + if (tbl->on_expr) + { + table_map outside_used_tables= tables_used_elsewhere | + tables_used_on_left; + bool multiple_matches= FALSE; + if (tbl->nested_join) + { + /* This is "... LEFT JOIN (join_nest) ON cond" */ + uint n; + if (!(n= eliminate_tables_for_list(join, cur_table, + &tbl->nested_join->join_list, TRUE, + tbl->nested_join->used_tables, + outside_used_tables, + &multiple_matches))) + { + mark_as_eliminated(join, tbl); + } + else + remaining_children++; + tbl->nested_join->n_tables= n; + } + else + { + /* This is "... LEFT JOIN tbl ON cond" */ + if (!(tbl->table->map & outside_used_tables) && + table_has_one_match(tbl->table, join->all_tables_map(), + &multiple_matches)) + { + mark_as_eliminated(join, tbl); + } + else + remaining_children++; + } + tables_used_on_left |= tbl->on_expr->used_tables(); + children_have_multiple_matches= children_have_multiple_matches || + multiple_matches; + } + else + { + DBUG_ASSERT(!tbl->nested_join); + remaining_children++; + } + + if (tbl->table) + *(cur_table++)= tbl->table; + } + + *multiple_matches |= children_have_multiple_matches; + + /* Try eliminating the nest we're called for */ + if (its_outer_join && !children_have_multiple_matches && + !(tables_in_list & tables_used_elsewhere)) + { + table_map bound_tables= join->const_table_map | (join->all_tables_map() & + ~tables_in_list); + table_map old_bound_tables; + TABLE **leaves_end= cur_table; + /* + Do the same as const table search table: try to expand the set of bound + tables until it covers all tables in the join_list + */ + do + { + old_bound_tables= bound_tables; + for (cur_table= leaves_arr; cur_table != leaves_end; cur_table++) + { + if (!((*cur_table)->map & join->eliminated_tables) && + table_has_one_match(*cur_table, bound_tables, multiple_matches)) + { + bound_tables |= (*cur_table)->map; + } + } + } while (old_bound_tables != bound_tables); + + if (!(tables_in_list & ~bound_tables)) + { + /* + This join_list can be eliminated. Signal about this to the caller by + returning number of tables. + */ + remaining_children= 0; + } + } + return remaining_children; +} + + +/* + Check if the table will produce at most one matching record + + SYNOPSIS + table_has_one_match() + table The [base] table being checked + bound_tables Tables that should be considered bound. + multiple_matches OUT Set to TRUE when there is no way we could + find find a limitation that would give us one-match + property. + + DESCRIPTION + Check if table will produce at most one matching record for each record + combination of tables in bound_tables bitmap. + + The check is based on ref analysis data, KEYUSE structures. We're + handling two cases: + + 1. Table has a UNIQUE KEY(uk_col_1, ... uk_col_N), and for each uk_col_i + there is a KEYUSE that represents a limitation in form + + table.uk_col_i = func(bound_tables) (X) + + 2. Same as above but we also handle limitations in form + + table.uk_col_i = func(bound_tables, uk_col_j1, ... uk_col_j2) (XX) + + where values of uk_col_jN are known to be bound because for them we + have an equality of form (X) or (XX). + + RETURN + TRUE Yes, at most one match + FALSE No +*/ + +static bool table_has_one_match(TABLE *table, table_map bound_tables, + bool *multiple_matches) +{ + KEYUSE *keyuse= table->reginfo.join_tab->keyuse; + if (keyuse) + { + while (keyuse->table == table) + { + uint key= keyuse->key; + key_part_map bound_parts=0; + uint n_unusable=0; + bool ft_key= test(keyuse->keypart == FT_KEYPART); + KEY *keyinfo= table->key_info + key; + KEYUSE *key_start = keyuse; + + do /* For each keypart and each way to read it */ + { + if (keyuse->type == KEYUSE_USABLE) + { + if(!(keyuse->used_tables & ~bound_tables) && + !(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)) + { + bound_parts |= keyuse->keypart_map; + } + } + else + n_unusable++; + keyuse++; + } while (keyuse->table == table && keyuse->key == key); + + if (ft_key || ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) + != HA_NOSAME)) + { + continue; + } + + if (bound_parts == PREV_BITS(key_part_map, keyinfo->key_parts) || + extra_keyuses_bind_all_keyparts(bound_tables, table, key_start, + keyuse, n_unusable, bound_parts)) + { + return TRUE; + } + } + } + return FALSE; +} + + +/* + Check if KEYUSE elemements with unusable==TRUE bind all parts of the key + + SYNOPSIS + + extra_keyuses_bind_all_keyparts() + bound_tables Tables which can be considered constants + table Table we're examining + key_start Start of KEYUSE array with elements describing the key + of interest + key_end End of the array + 1 + n_keyuses Number of elements in the array that have unusable==TRUE + bound_parts Key parts whose values are known to be bound. + + DESCRIPTION + Check if unusable KEYUSE elements cause all parts of key to be bound. An + unusable keyuse element makes a keypart bound when it + represents the following: + + keyXpartY=func(bound_columns, preceding_tables) + + RETURN + TRUE Yes, at most one match + FALSE No +*/ + +static bool +extra_keyuses_bind_all_keyparts(table_map bound_tables, TABLE *table, + KEYUSE *key_start, KEYUSE *key_end, + uint n_keyuses, table_map bound_parts) +{ + /* + We need + - some 'unusable' KEYUSE elements to work on + - some keyparts to be already bound to start inferences: + */ + if (n_keyuses && bound_parts) + { + KEY *keyinfo= table->key_info + key_start->key; + bool bound_more_parts; + do + { + bound_more_parts= FALSE; + for (KEYUSE *k= key_start; k!=key_end; k++) + { + if (k->type == KEYUSE_UNKNOWN) + { + Field_processor_info fp= {table, k->key, k->keypart, 0, 0}; + if (k->val->walk(&Item::check_column_usage_processor, FALSE, + (uchar*)&fp)) + k->type= KEYUSE_NO_BIND; + else + { + k->used_tables= fp.used_tables; + k->keypart_map= fp.needed_key_parts; + k->type= KEYUSE_BIND; + } + } + + if (k->type == KEYUSE_BIND) + { + /* + If this is a binding keyuse, such that + - all tables it refers to are bound, + - all parts it refers to are bound + - but the key part it binds is not itself bound + */ + if (!(k->used_tables & ~bound_tables) && + !(k->keypart_map & ~bound_parts) && + !(bound_parts & key_part_map(1) << k->keypart)) + { + bound_parts|= key_part_map(1) << k->keypart; + if (bound_parts == PREV_BITS(key_part_map, keyinfo->key_parts)) + return TRUE; + bound_more_parts= TRUE; + } + } + } + } while (bound_more_parts); + } + return FALSE; +} + + +/* + Mark one table or the whole join nest as eliminated. +*/ +static void mark_as_eliminated(JOIN *join, TABLE_LIST *tbl) +{ + TABLE *table; + /* + NOTE: there are TABLE_LIST object that have + tbl->table!= NULL && tbl->nested_join!=NULL and + tbl->table == tbl->nested_join->join_list->element(..)->table + */ + if (tbl->nested_join) + { + TABLE_LIST *child; + List_iterator<TABLE_LIST> it(tbl->nested_join->join_list); + while ((child= it++)) + mark_as_eliminated(join, child); + } + else if ((table= tbl->table)) + { + JOIN_TAB *tab= tbl->table->reginfo.join_tab; + if (!(join->const_table_map & tab->table->map)) + { + DBUG_PRINT("info", ("Eliminated table %s", table->alias)); + tab->type= JT_CONST; + join->eliminated_tables |= table->map; + join->const_table_map|= table->map; + set_position(join, join->const_tables++, tab, (KEYUSE*)0); + } + } + + if (tbl->on_expr) + tbl->on_expr->walk(&Item::mark_as_eliminated_processor, FALSE, NULL); +} + +/** + @} (end of group Table_Elimination) +*/ + === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2009-04-25 10:05:32 +0000 +++ b/sql/sql_lex.cc 2009-06-30 15:09:36 +0000 @@ -1778,7 +1778,7 @@ void st_select_lex_unit::exclude_tree() 'last' should be reachable from this st_select_lex_node */ -void st_select_lex::mark_as_dependent(st_select_lex *last) +void st_select_lex::mark_as_dependent(st_select_lex *last, Item *dependency) { /* Mark all selects from resolved to 1 before select where was @@ -1804,6 +1804,8 @@ void st_select_lex::mark_as_dependent(st } is_correlated= TRUE; this->master_unit()->item->is_correlated= TRUE; + if (dependency) + this->master_unit()->item->refers_to.push_back(dependency); } bool st_select_lex_node::set_braces(bool value) { return 1; } === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2009-03-17 20:29:24 +0000 +++ b/sql/sql_lex.h 2009-06-30 15:09:36 +0000 @@ -743,7 +743,7 @@ public: return master_unit()->return_after_parsing(); } - void mark_as_dependent(st_select_lex *last); + void mark_as_dependent(st_select_lex *last, Item *dependency); bool set_braces(bool value); bool inc_in_sum_expr(); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-05-19 09:28:05 +0000 +++ b/sql/sql_select.cc 2009-06-30 15:09:36 +0000 @@ -60,7 +60,6 @@ static bool update_ref_and_keys(THD *thd table_map table_map, SELECT_LEX *select_lex, st_sargable_param **sargables); static int sort_keyuse(KEYUSE *a,KEYUSE *b); -static void set_position(JOIN *join,uint index,JOIN_TAB *table,KEYUSE *key); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, table_map used_tables); static bool choose_plan(JOIN *join,table_map join_tables); @@ -2381,6 +2380,13 @@ mysql_select(THD *thd, Item ***rref_poin } else { + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + if (!(join= new JOIN(thd, fields, select_options, result))) DBUG_RETURN(TRUE); thd_proc_info(thd, "init"); @@ -2468,6 +2474,7 @@ static ha_rows get_quick_record_count(TH DBUG_RETURN(HA_POS_ERROR); /* This shouldn't happend */ } + /* This structure is used to collect info on potentially sargable predicates in order to check whether they become sargable after @@ -2646,24 +2653,31 @@ make_join_statistics(JOIN *join, TABLE_L ~outer_join, join->select_lex, &sargables)) goto error; - /* Read tables with 0 or 1 rows (system tables) */ join->const_table_map= 0; + join->const_tables= const_count; + eliminate_tables(join); + const_count= join->const_tables; + found_const_table_map= join->const_table_map; + /* Read tables with 0 or 1 rows (system tables) */ for (POSITION *p_pos=join->positions, *p_end=p_pos+const_count; p_pos < p_end ; p_pos++) { - int tmp; s= p_pos->table; - s->type=JT_SYSTEM; - join->const_table_map|=s->table->map; - if ((tmp=join_read_const_table(s, p_pos))) + if (! (s->table->map & join->eliminated_tables)) { - if (tmp > 0) - goto error; // Fatal error + int tmp; + s->type=JT_SYSTEM; + join->const_table_map|=s->table->map; + if ((tmp=join_read_const_table(s, p_pos))) + { + if (tmp > 0) + goto error; // Fatal error + } + else + found_const_table_map|= s->table->map; } - else - found_const_table_map|= s->table->map; } /* loop until no more const tables are found */ @@ -2688,7 +2702,8 @@ make_join_statistics(JOIN *join, TABLE_L substitution of a const table the key value happens to be null then we can state that there are no matches for this equi-join. */ - if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map) + if ((keyuse= s->keyuse) && *s->on_expr_ref && !s->embedding_map && + !(table->map & join->eliminated_tables)) { /* When performing an outer join operation if there are no matching rows @@ -2747,14 +2762,16 @@ make_join_statistics(JOIN *join, TABLE_L { start_keyuse=keyuse; key=keyuse->key; - s->keys.set_bit(key); // QQ: remove this ? + if (keyuse->type == KEYUSE_USABLE) + s->keys.set_bit(key); // QQ: remove this ? refs=0; const_ref.clear_all(); eq_part.clear_all(); do { - if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize) + if (keyuse->type == KEYUSE_USABLE && + keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize) { if (!((~found_const_table_map) & keyuse->used_tables)) const_ref.set_bit(keyuse->keypart); @@ -2954,17 +2971,35 @@ typedef struct key_field_t { */ bool null_rejecting; bool *cond_guard; /* See KEYUSE::cond_guard */ + enum keyuse_type type; /* See KEYUSE::type */ } KEY_FIELD; -/* Values in optimize */ -#define KEY_OPTIMIZE_EXISTS 1 -#define KEY_OPTIMIZE_REF_OR_NULL 2 /** Merge new key definitions to old ones, remove those not used in both. This is called for OR between different levels. + That is, the function operates on an array of KEY_FIELD elements which has + two parts: + + $LEFT_PART $RIGHT_PART + +-----------------------+-----------------------+ + start new_fields end + + $LEFT_PART and $RIGHT_PART are arrays that have KEY_FIELD elements for two + parts of the OR condition. Our task is to produce an array of KEY_FIELD + elements that would correspond to "$LEFT_PART OR $RIGHT_PART". + + The rules for combining elements are as follows: + (keyfieldA1 AND keyfieldA2 AND ...) OR (keyfieldB1 AND keyfieldB2 AND ...)= + AND_ij (keyfieldA_i OR keyfieldB_j) + + We discard all (keyfieldA_i OR keyfieldB_j) that refer to different + fields. For those referring to the same field, the logic is as follows: + + t.keycol= + To be able to do 'ref_or_null' we merge a comparison of a column and 'column IS NULL' to one test. This is useful for sub select queries that are internally transformed to something like:. @@ -3029,13 +3064,18 @@ merge_key_fields(KEY_FIELD *start,KEY_FI KEY_OPTIMIZE_REF_OR_NULL)); old->null_rejecting= (old->null_rejecting && new_fields->null_rejecting); + /* + The conditions are the same, hence their usabilities should + be, too (TODO: shouldn't that apply to the above + null_rejecting and optimize attributes?) + */ + DBUG_ASSERT(old->type == new_fields->type); } } else if (old->eq_func && new_fields->eq_func && old->val->eq_by_collation(new_fields->val, old->field->binary(), old->field->charset())) - { old->level= and_level; old->optimize= ((old->optimize & new_fields->optimize & @@ -3044,10 +3084,15 @@ merge_key_fields(KEY_FIELD *start,KEY_FI KEY_OPTIMIZE_REF_OR_NULL)); old->null_rejecting= (old->null_rejecting && new_fields->null_rejecting); + // "t.key_col=const" predicates are always usable + DBUG_ASSERT(old->type == KEYUSE_USABLE && + new_fields->type == KEYUSE_USABLE); } else if (old->eq_func && new_fields->eq_func && - ((old->val->const_item() && old->val->is_null()) || - new_fields->val->is_null())) + ((new_fields->type == KEYUSE_USABLE && + old->val->const_item() && old->val->is_null()) || + ((old->type == KEYUSE_USABLE && new_fields->val->is_null())))) + /* TODO ^ why is the above asymmetric, why const_item()? */ { /* field = expression OR field IS NULL */ old->level= and_level; @@ -3118,6 +3163,7 @@ add_key_field(KEY_FIELD **key_fields,uin table_map usable_tables, SARGABLE_PARAM **sargables) { uint exists_optimize= 0; + bool optimizable=0; if (!(field->flags & PART_KEY_FLAG)) { // Don't remove column IS NULL on a LEFT JOIN table @@ -3130,15 +3176,12 @@ add_key_field(KEY_FIELD **key_fields,uin else { table_map used_tables=0; - bool optimizable=0; for (uint i=0; i<num_values; i++) { used_tables|=(value[i])->used_tables(); if (!((value[i])->used_tables() & (field->table->map | RAND_TABLE_BIT))) optimizable=1; } - if (!optimizable) - return; if (!(usable_tables & field->table->map)) { if (!eq_func || (*value)->type() != Item::NULL_ITEM || @@ -3151,7 +3194,8 @@ add_key_field(KEY_FIELD **key_fields,uin JOIN_TAB *stat=field->table->reginfo.join_tab; key_map possible_keys=field->key_start; possible_keys.intersect(field->table->keys_in_use_for_query); - stat[0].keys.merge(possible_keys); // Add possible keys + if (optimizable) + stat[0].keys.merge(possible_keys); // Add possible keys /* Save the following cases: @@ -3244,6 +3288,7 @@ add_key_field(KEY_FIELD **key_fields,uin (*key_fields)->val= *value; (*key_fields)->level= and_level; (*key_fields)->optimize= exists_optimize; + (*key_fields)->type= optimizable? KEYUSE_USABLE : KEYUSE_UNKNOWN; /* If the condition has form "tbl.keypart = othertbl.field" and othertbl.field can be NULL, there will be no matches if othertbl.field @@ -3555,6 +3600,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; keyuse.null_rejecting= key_field->null_rejecting; keyuse.cond_guard= key_field->cond_guard; + keyuse.type= key_field->type; VOID(insert_dynamic(keyuse_array,(uchar*) &keyuse)); } } @@ -3563,7 +3609,6 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array } -#define FT_KEYPART (MAX_REF_PARTS+10) static void add_ft_keys(DYNAMIC_ARRAY *keyuse_array, @@ -3622,6 +3667,7 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array, keyuse.used_tables=cond_func->key_item()->used_tables(); keyuse.optimize= 0; keyuse.keypart_map= 0; + keyuse.type= KEYUSE_USABLE; VOID(insert_dynamic(keyuse_array,(uchar*) &keyuse)); } @@ -3636,6 +3682,13 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) return (int) (a->key - b->key); if (a->keypart != b->keypart) return (int) (a->keypart - b->keypart); + + // Usable ones go before the unusable + int a_ok= test(a->type == KEYUSE_USABLE); + int b_ok= test(b->type == KEYUSE_USABLE); + if (a_ok != b_ok) + return a_ok? -1 : 1; + // Place const values before other ones if ((res= test((a->used_tables & ~OUTER_REF_TABLE_BIT)) - test((b->used_tables & ~OUTER_REF_TABLE_BIT)))) @@ -3846,7 +3899,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR found_eq_constant=0; for (i=0 ; i < keyuse->elements-1 ; i++,use++) { - if (!use->used_tables && use->optimize != KEY_OPTIMIZE_REF_OR_NULL) + if (use->type == KEYUSE_USABLE && !use->used_tables && + use->optimize != KEY_OPTIMIZE_REF_OR_NULL) use->table->const_key_parts[use->key]|= use->keypart_map; if (use->keypart != FT_KEYPART) { @@ -3870,7 +3924,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR /* Save ptr to first use */ if (!use->table->reginfo.join_tab->keyuse) use->table->reginfo.join_tab->keyuse=save_pos; - use->table->reginfo.join_tab->checked_keys.set_bit(use->key); + if (use->type == KEYUSE_USABLE) + use->table->reginfo.join_tab->checked_keys.set_bit(use->key); save_pos++; } i=(uint) (save_pos-(KEYUSE*) keyuse->buffer); @@ -3900,7 +3955,7 @@ static void optimize_keyuse(JOIN *join, To avoid bad matches, we don't make ref_table_rows less than 100. */ keyuse->ref_table_rows= ~(ha_rows) 0; // If no ref - if (keyuse->used_tables & + if (keyuse->type == KEYUSE_USABLE && keyuse->used_tables & (map= (keyuse->used_tables & ~join->const_table_map & ~OUTER_REF_TABLE_BIT))) { @@ -3990,8 +4045,7 @@ add_group_and_distinct_keys(JOIN *join, /** Save const tables first as used tables. */ -static void -set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) +void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key) { join->positions[idx].table= table; join->positions[idx].key=key; @@ -4093,7 +4147,8 @@ best_access_path(JOIN *join, if 1. expression doesn't refer to forward tables 2. we won't get two ref-or-null's */ - if (!(remaining_tables & keyuse->used_tables) && + if (keyuse->type == KEYUSE_USABLE && + !(remaining_tables & keyuse->used_tables) && !(ref_or_null_part && (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL))) { @@ -5547,7 +5602,8 @@ static bool create_ref_for_key(JOIN *joi */ do { - if (!(~used_tables & keyuse->used_tables)) + if (!(~used_tables & keyuse->used_tables) && + keyuse->type == KEYUSE_USABLE) { if (keyparts == keyuse->keypart && !(found_part_ref_or_null & keyuse->optimize)) @@ -5597,9 +5653,11 @@ static bool create_ref_for_key(JOIN *joi uint i; for (i=0 ; i < keyparts ; keyuse++,i++) { - while (keyuse->keypart != i || - ((~used_tables) & keyuse->used_tables)) + while (keyuse->keypart != i || ((~used_tables) & keyuse->used_tables) || + !(keyuse->type == KEYUSE_USABLE)) + { keyuse++; /* Skip other parts */ + } uint maybe_null= test(keyinfo->key_part[i].null_bit); j->ref.items[i]=keyuse->val; // Save for cond removal @@ -5757,6 +5815,7 @@ JOIN::make_simple_join(JOIN *parent, TAB tables= 1; const_tables= 0; const_table_map= 0; + eliminated_tables= 0; tmp_table_param.field_count= tmp_table_param.sum_func_count= tmp_table_param.func_count= 0; tmp_table_param.copy_field= tmp_table_param.copy_field_end=0; @@ -6021,7 +6080,7 @@ make_outerjoin_info(JOIN *join) } if (!tab->first_inner) tab->first_inner= nested_join->first_nested; - if (++nested_join->counter < nested_join->join_list.elements) + if (++nested_join->counter < nested_join->n_tables) break; /* Table tab is the last inner table for nested join. */ nested_join->first_nested->last_inner= tab; @@ -8575,6 +8634,8 @@ simplify_joins(JOIN *join, List<TABLE_LI conds= simplify_joins(join, &nested_join->join_list, conds, top); used_tables= nested_join->used_tables; not_null_tables= nested_join->not_null_tables; + /* The following two might become unequal after table elimination: */ + nested_join->n_tables= nested_join->join_list.elements; } else { @@ -8733,7 +8794,7 @@ static uint build_bitmap_for_nested_join with anything) 2. we could run out bits in nested_join_map otherwise. */ - if (nested_join->join_list.elements != 1) + if (nested_join->n_tables != 1) { nested_join->nj_map= (nested_join_map) 1 << first_unused++; first_unused= build_bitmap_for_nested_joins(&nested_join->join_list, @@ -8894,7 +8955,7 @@ static bool check_interleaving_with_nj(J join->cur_embedding_map |= next_emb->nested_join->nj_map; } - if (next_emb->nested_join->join_list.elements != + if (next_emb->nested_join->n_tables != next_emb->nested_join->counter) break; @@ -8926,9 +8987,23 @@ static void restore_prev_nj_state(JOIN_T JOIN *join= last->join; while (last_emb) { + /* + psergey-elim: (nevermind) + new_prefix= cur_prefix & ~last; + if (!(new_prefix & cur_table_map)) // removed last inner table + { + join->cur_embedding_map&= ~last_emb->nested_join->nj_map; + } + else (current) + { + // Won't hurt doing it all the time: + join->cur_embedding_map |= ...; + } + else + */ if (!(--last_emb->nested_join->counter)) join->cur_embedding_map&= ~last_emb->nested_join->nj_map; - else if (last_emb->nested_join->join_list.elements-1 == + else if (last_emb->nested_join->n_tables-1 == last_emb->nested_join->counter) join->cur_embedding_map|= last_emb->nested_join->nj_map; else @@ -16202,6 +16277,14 @@ static void select_describe(JOIN *join, tmp3.length(0); quick_type= -1; + + /* Don't show eliminated tables */ + if (table->map & join->eliminated_tables) + { + used_tables|=table->map; + continue; + } + item_list.empty(); /* id */ item_list.push_back(new Item_uint((uint32) @@ -16524,8 +16607,11 @@ static void select_describe(JOIN *join, unit; unit= unit->next_unit()) { - if (mysql_explain_union(thd, unit, result)) - DBUG_VOID_RETURN; + if (!(unit->item && unit->item->eliminated)) + { + if (mysql_explain_union(thd, unit, result)) + DBUG_VOID_RETURN; + } } DBUG_VOID_RETURN; } @@ -16566,7 +16652,6 @@ bool mysql_explain_union(THD *thd, SELEC unit->fake_select_lex->options|= SELECT_DESCRIBE; if (!(res= unit->prepare(thd, result, SELECT_NO_UNLOCK | SELECT_DESCRIBE))) res= unit->exec(); - res|= unit->cleanup(); } else { @@ -16599,6 +16684,7 @@ bool mysql_explain_union(THD *thd, SELEC */ static void print_join(THD *thd, + table_map eliminated_tables, String *str, List<TABLE_LIST> *tables, enum_query_type query_type) @@ -16614,12 +16700,33 @@ static void print_join(THD *thd, *t= ti++; DBUG_ASSERT(tables->elements >= 1); - (*table)->print(thd, str, query_type); + /* + Assert that the first table in the list isn't eliminated. This comes from + the fact that the first table can't be inner table of an outer join. + */ + DBUG_ASSERT(!eliminated_tables || + !(((*table)->table && ((*table)->table->map & eliminated_tables)) || + ((*table)->nested_join && !((*table)->nested_join->used_tables & + ~eliminated_tables)))); + (*table)->print(thd, eliminated_tables, str, query_type); TABLE_LIST **end= table + tables->elements; for (TABLE_LIST **tbl= table + 1; tbl < end; tbl++) { TABLE_LIST *curr= *tbl; + /* + The "eliminated_tables &&" check guards againist the case of + printing the query for CREATE VIEW. We do that without having run + JOIN::optimize() and so will have nested_join->used_tables==0. + */ + if (eliminated_tables && + ((curr->table && (curr->table->map & eliminated_tables)) || + (curr->nested_join && !(curr->nested_join->used_tables & + ~eliminated_tables)))) + { + continue; + } + if (curr->outer_join) { /* MySQL converts right to left joins */ @@ -16629,7 +16736,7 @@ static void print_join(THD *thd, str->append(STRING_WITH_LEN(" straight_join ")); else str->append(STRING_WITH_LEN(" join ")); - curr->print(thd, str, query_type); + curr->print(thd, eliminated_tables, str, query_type); if (curr->on_expr) { str->append(STRING_WITH_LEN(" on(")); @@ -16683,12 +16790,13 @@ Index_hint::print(THD *thd, String *str) @param str string where table should be printed */ -void TABLE_LIST::print(THD *thd, String *str, enum_query_type query_type) +void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str, + enum_query_type query_type) { if (nested_join) { str->append('('); - print_join(thd, str, &nested_join->join_list, query_type); + print_join(thd, eliminated_tables, str, &nested_join->join_list, query_type); str->append(')'); } else @@ -16830,7 +16938,7 @@ void st_select_lex::print(THD *thd, Stri { str->append(STRING_WITH_LEN(" from ")); /* go through join tree */ - print_join(thd, str, &top_join_list, query_type); + print_join(thd, join? join->eliminated_tables: 0, str, &top_join_list, query_type); } else if (where) { === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2009-04-25 10:05:32 +0000 +++ b/sql/sql_select.h 2009-06-30 15:09:36 +0000 @@ -28,6 +28,45 @@ #include "procedure.h" #include <myisam.h> +#define FT_KEYPART (MAX_REF_PARTS+10) +/* Values in optimize */ +#define KEY_OPTIMIZE_EXISTS 1 +#define KEY_OPTIMIZE_REF_OR_NULL 2 + +/* KEYUSE element types */ +enum keyuse_type +{ + /* + val refers to the same table, this is either KEYUSE_BIND or KEYUSE_NO_BIND + type, we didn't determine which one yet. + */ + KEYUSE_UNKNOWN= 0, + /* + 'regular' keyuse, i.e. it represents one of the following + * t.keyXpartY = func(constants, other-tables) + * t.keyXpartY IS NULL + * t.keyXpartY = func(constants, other-tables) OR t.keyXpartY IS NULL + and can be used to construct ref acces + */ + KEYUSE_USABLE, + /* + The keyuse represents a condition in form: + + t.uniq_keyXpartY = func(other parts of uniq_keyX) + + This can't be used to construct uniq_keyX but we could use it to determine + that the table will produce at most one match. + */ + KEYUSE_BIND, + /* + Keyuse that's not usable for ref access and doesn't meet the criteria of + KEYUSE_BIND. Examples: + t.keyXpartY = func(t.keyXpartY) + t.keyXpartY = func(column of t that's not covered by keyX) + */ + KEYUSE_NO_BIND +}; + typedef struct keyuse_t { TABLE *table; Item *val; /**< or value if no field */ @@ -51,6 +90,15 @@ typedef struct keyuse_t { NULL - Otherwise (the source equality can't be turned off) */ bool *cond_guard; + /* + 1 <=> This keyuse can be used to construct key access. + 0 <=> Otherwise. Currently unusable KEYUSEs represent equalities + where one table column refers to another one, like this: + t.keyXpartA=func(t.keyXpartB) + This equality cannot be used for index access but is useful + for table elimination. + */ + enum keyuse_type type; } KEYUSE; class store_key; @@ -210,7 +258,7 @@ typedef struct st_join_table { JOIN *join; /** Bitmap of nested joins this table is part of */ nested_join_map embedding_map; - + void cleanup(); inline bool is_using_loose_index_scan() { @@ -285,7 +333,15 @@ public: fetching data from a cursor */ bool resume_nested_loop; - table_map const_table_map,found_const_table_map; + table_map const_table_map; + /* + Constant tables for which we have found a row (as opposed to those for + which we didn't). + */ + table_map found_const_table_map; + + /* Tables removed by table elimination. Set to 0 before the elimination. */ + table_map eliminated_tables; /* Bitmap of all inner tables from outer joins */ @@ -425,6 +481,7 @@ public: table= 0; tables= 0; const_tables= 0; + eliminated_tables= 0; join_list= 0; sort_and_group= 0; first_record= 0; @@ -530,6 +587,10 @@ public: return (unit == &thd->lex->unit && (unit->fake_select_lex == 0 || select_lex == unit->fake_select_lex)); } + inline table_map all_tables_map() + { + return (table_map(1) << tables) - 1; + } private: bool make_simple_join(JOIN *join, TABLE *tmp_table); }; @@ -730,9 +791,12 @@ bool error_if_full_join(JOIN *join); int report_error(TABLE *table, int error); int safe_index_read(JOIN_TAB *tab); COND *remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value); +void set_position(JOIN *join,uint idx,JOIN_TAB *table,KEYUSE *key); inline bool optimizer_flag(THD *thd, uint flag) { return (thd->variables.optimizer_switch & flag); } +void eliminate_tables(JOIN *join); + === modified file 'sql/table.h' --- a/sql/table.h 2009-02-19 09:01:25 +0000 +++ b/sql/table.h 2009-06-30 15:09:36 +0000 @@ -1366,7 +1366,8 @@ struct TABLE_LIST return (derived || view || schema_table || (create && !table->db_stat) || !table); } - void print(THD *thd, String *str, enum_query_type query_type); + void print(THD *thd, table_map eliminated_tables, String *str, + enum_query_type query_type); bool check_single_table(TABLE_LIST **table, table_map map, TABLE_LIST *view); bool set_insert_values(MEM_ROOT *mem_root); @@ -1615,7 +1616,11 @@ public: typedef struct st_nested_join { List<TABLE_LIST> join_list; /* list of elements in the nested join */ - table_map used_tables; /* bitmap of tables in the nested join */ + /* + Bitmap of tables within this nested join (including those embedded within + its children), including tables removed by table elimination. + */ + table_map used_tables; table_map not_null_tables; /* tables that rejects nulls */ struct st_join_table *first_nested;/* the first nested table in the plan */ /* @@ -1626,6 +1631,11 @@ typedef struct st_nested_join Before each use the counters are zeroed by reset_nj_counters. */ uint counter; + /* + Number of elements in join_list that were not (or contain table(s) that + weren't) removed by table elimination. + */ + uint n_tables; nested_join_map nj_map; /* Bit used to identify this nested join*/ } NESTED_JOIN;

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (psergey:2724)
by Sergey Petrunia 30 Jun '09

30 Jun '09

#At lp:maria based on revid:psergey@askmonty.org-20090625200729-u11xpwwn5ebddx09 2724 Sergey Petrunia 2009-06-30 Testing commit email modified: sql/sql_select.cc === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2009-06-25 20:07:29 +0000 +++ b/sql/sql_select.cc 2009-06-30 15:02:15 +0000 @@ -24,6 +24,8 @@ @{ */ +#error Testing commit mails + #ifdef USE_PRAGMA_IMPLEMENTATION #pragma implementation // gcc: Class implementation #endif

1 0