# name : innodb_expand_fast_index_creation.patch # maintainer : Alexey # # Expands the applicability of InnoDB fast index creation to mysqldump, # ALTER TABLE and OPTIMIZE TABLE. # # --- a/client/client_priv.h +++ b/client/client_priv.h @@ -92,6 +92,7 @@ OPT_NO_REMOVE_EOL_CARRET, OPT_DEFAULT_AUTH, OPT_DEFAULT_PLUGIN, + OPT_INNODB_OPTIMIZE_KEYS, OPT_MAX_CLIENT_OPTION }; --- a/client/mysqldump.c +++ b/client/mysqldump.c @@ -47,6 +47,7 @@ #include #include #include +#include #include "client_priv.h" #include "mysql.h" @@ -145,6 +146,8 @@ static my_bool server_supports_sql_no_fcache= FALSE; +static my_bool opt_innodb_optimize_keys= FALSE; + /* Dynamic_string wrapper functions. In this file use these wrappers, they will terminate the process if there is @@ -190,6 +193,8 @@ HASH ignore_table; +LIST *skipped_keys_list; + static struct my_option my_long_options[] = { {"all-databases", 'A', @@ -353,6 +358,11 @@ "in dump produced with --dump-slave.", &opt_include_master_host_port, &opt_include_master_host_port, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS, + "Use InnoDB fast index creation by creating secondary indexes after " + "dumping the data.", + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG, + 0, 0, 0, 0, 0, 0}, {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.", &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, @@ -2240,6 +2250,189 @@ } /* + Parse the specified key definition string and check if the key indexes + any of the columns from ignored_columns. +*/ +static my_bool contains_ignored_column(HASH *ignored_columns, char *keydef) +{ + char *leftp, *rightp; + + if ((leftp = strchr(keydef, '(')) && + (rightp = strchr(leftp, ')')) && + rightp > leftp + 3 && /* (`...`) */ + leftp[1] == '`' && + rightp[-1] == '`' && + my_hash_search(ignored_columns, (uchar *) leftp + 2, rightp - leftp - 3)) + return TRUE; + + return FALSE; +} + + +/* + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string + and store them into a temporary list to be used later. + + SYNOPSIS + skip_secondary_keys() + create_str SHOW CREATE TABLE output + has_pk TRUE, if the table has PRIMARY KEY + (or UNIQUE key on non-nullable columns) + + + DESCRIPTION + + Stores all lines starting with "KEY" or "UNIQUE KEY" + into skipped_keys_list and removes them from the input string. + Ignoring FOREIGN KEYS constraints when creating the table is ok, because + mysqldump sets foreign_key_checks to 0 anyway. +*/ + +static void skip_secondary_keys(char *create_str, my_bool has_pk) +{ + char *ptr, *strend; + char *last_comma = NULL; + HASH ignored_columns; + my_bool pk_processed= FALSE; + + if (my_hash_init(&ignored_columns, charset_info, 16, 0, 0, + (my_hash_get_key) get_table_key, my_free, 0)) + exit(EX_EOM); + + strend= create_str + strlen(create_str); + + ptr= create_str; + while (*ptr) + { + char *tmp, *orig_ptr, c; + my_bool is_unique= FALSE; + + orig_ptr= ptr; + /* Skip leading whitespace */ + while (*ptr && my_isspace(charset_info, *ptr)) + ptr++; + + /* Read the next line */ + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++); + + c= *tmp; + *tmp= '\0'; /* so strstr() only processes the current line */ + + /* Is it a secondary index definition? */ + if (c == '\n' && + (((is_unique= !strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ")-1)) && + (pk_processed || !has_pk)) || + !strncmp(ptr, "KEY ", sizeof("KEY ") - 1)) && + !contains_ignored_column(&ignored_columns, ptr)) + { + char *data, *end= tmp - 1; + + /* Remove the trailing comma */ + if (*end == ',') + end--; + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE)); + skipped_keys_list= list_cons(data, skipped_keys_list); + + memmove(orig_ptr, tmp + 1, strend - tmp); + ptr= orig_ptr; + strend-= tmp + 1 - ptr; + + /* Remove the comma on the previos line */ + if (last_comma != NULL) + { + *last_comma= ' '; + } + } + else + { + char *end; + + if (last_comma != NULL && *ptr != ')') + { + /* + It's not the last line of CREATE TABLE, so we have skipped a key + definition. We have to restore the last removed comma. + */ + *last_comma= ','; + } + + if ((has_pk && is_unique && !pk_processed) || + !strncmp(ptr, "PRIMARY KEY ", sizeof("PRIMARY KEY ") - 1)) + pk_processed= TRUE; + + if (strstr(ptr, "AUTO_INCREMENT") && *ptr == '`') + { + /* + If a secondary key is defined on this column later, + it cannot be skipped, as CREATE TABLE would fail on import. + */ + for (end= ptr + 1; *end != '`' && *end != '\0'; end++); + if (*end == '`' && end > ptr + 1 && + my_hash_insert(&ignored_columns, + (uchar *) my_strndup(ptr + 1, + end - ptr - 1, MYF(0)))) + { + exit(EX_EOM); + } + } + + *tmp= c; + + if (tmp[-1] == ',') + last_comma= tmp - 1; + ptr= (*tmp == '\0') ? tmp : tmp + 1; + } + } + + my_hash_free(&ignored_columns); +} + +/* + Check if the table has a primary key defined either explicitly or + implicitly (i.e. a unique key on non-nullable columns). + + SYNOPSIS + my_bool has_primary_key(const char *table_name) + + table_name quoted table name + + RETURNS TRUE if the table has a primary key + + DESCRIPTION +*/ + +static my_bool has_primary_key(const char *table_name) +{ + MYSQL_RES *res= NULL; + MYSQL_ROW row; + char query_buff[QUERY_LENGTH]; + my_bool has_pk= TRUE; + + my_snprintf(query_buff, sizeof(query_buff), + "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE " + "TABLE_SCHEMA=DATABASE() AND TABLE_NAME='%s' AND " + "COLUMN_KEY='PRI'", table_name); + if (mysql_query(mysql, query_buff) || !(res= mysql_store_result(mysql)) || + !(row= mysql_fetch_row(res))) + { + fprintf(stderr, "Warning: Couldn't determine if table %s has a " + "primary key (%s). " + "--innodb-optimize-keys may work inefficiently.\n", + table_name, mysql_error(mysql)); + goto cleanup; + } + + has_pk= atoi(row[0]) > 0; + +cleanup: + if (res) + mysql_free_result(res); + + return has_pk; +} + + +/* get_table_structure -- retrievs database structure, prints out corresponding CREATE statement and fills out insert_pat if the table is the type we will be dumping. @@ -2276,6 +2469,7 @@ int len; MYSQL_RES *result; MYSQL_ROW row; + my_bool has_pk= FALSE; DBUG_ENTER("get_table_structure"); DBUG_PRINT("enter", ("db: %s table: %s", db, table)); @@ -2317,6 +2511,9 @@ result_table= quote_name(table, table_buff, 1); opt_quoted_table= quote_name(table, table_buff2, 0); + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) + has_pk= has_primary_key(table); + if (opt_order_by_primary) order_by= primary_key_fields(result_table); @@ -2480,6 +2677,9 @@ row= mysql_fetch_row(result); + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) + skip_secondary_keys(row[1], has_pk); + fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" : "/*!40101 SET @saved_cs_client = @@character_set_client */;\n" "/*!40101 SET character_set_client = utf8 */;\n" @@ -3574,6 +3774,27 @@ goto err; } + /* Perform delayed secondary index creation for --innodb-optimize-keys */ + if (skipped_keys_list) + { + uint keys; + skipped_keys_list= list_reverse(skipped_keys_list); + fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table); + for (keys= list_length(skipped_keys_list); keys > 0; keys--) + { + LIST *node= skipped_keys_list; + char *def= node->data; + + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n"); + + skipped_keys_list= list_delete(skipped_keys_list, node); + my_free(def); + my_free(node); + } + + DBUG_ASSERT(skipped_keys_list == NULL); + } + /* Moved enable keys to before unlock per bug 15977 */ if (opt_disable_keys) { --- /dev/null +++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result @@ -0,0 +1,367 @@ +# +# Test the --innodb-optimize-keys option. +# +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; +###################################### + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `b` (`b`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +###################################### +DROP TABLE t1; +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t2 VALUES (0), (1), (2); +CREATE TABLE t1 ( +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +a INT, b VARCHAR(255), c DECIMAL(10,3), +KEY (b), +UNIQUE KEY uniq(c,a), +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE +) ENGINE=InnoDB; +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); +###################################### + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `a` int(11) DEFAULT NULL, + `b` varchar(255) DEFAULT NULL, + `c` decimal(10,3) DEFAULT NULL, + PRIMARY KEY (`id`), + CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200); +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t2`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t2` ( + `a` int(11) NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t2` WRITE; +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; +INSERT INTO `t2` VALUES (0),(1),(2); +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +###################################### +DROP TABLE t1, t2; +CREATE TABLE t1 ( +id INT NOT NULL AUTO_INCREMENT, +KEY (id) +) ENGINE=InnoDB; +CREATE TABLE t2 ( +id INT NOT NULL AUTO_INCREMENT, +UNIQUE KEY (id) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (), (), (); +INSERT INTO t2 VALUES (), (), (); +###################################### + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + KEY `id` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (1),(2),(3); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t2`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t2` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + UNIQUE KEY `id` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t2` WRITE; +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; +INSERT INTO `t2` VALUES (1),(2),(3); +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +###################################### +DROP TABLE t1, t2; +CREATE TABLE t1 ( +a INT NOT NULL, +UNIQUE KEY (a)) ENGINE=InnoDB; +CREATE TABLE t2 ( +a INT NOT NULL, +b INT NOT NULL, +UNIQUE KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t3 ( +a INT, +b INT, +UNIQUE KEY (a,b)) ENGINE=InnoDB; +CREATE TABLE t4 ( +a INT NOT NULL, +b INT NOT NULL, +PRIMARY KEY (a,b), +UNIQUE KEY(b)) ENGINE=InnoDB; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE +TABLE_SCHEMA=DATABASE() AND +TABLE_NAME='t1' AND +COLUMN_KEY='PRI'; +COUNT(*) +1 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE +TABLE_SCHEMA=DATABASE() AND +TABLE_NAME='t2' AND +COLUMN_KEY='PRI'; +COUNT(*) +2 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE +TABLE_SCHEMA=DATABASE() AND +TABLE_NAME='t3' AND +COLUMN_KEY='PRI'; +COUNT(*) +0 +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE +TABLE_SCHEMA=DATABASE() AND +TABLE_NAME='t4' AND +COLUMN_KEY='PRI'; +COUNT(*) +2 +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); +INSERT INTO t3 SELECT * FROM t2; +INSERT INTO t4 SELECT * FROM t2; +###################################### + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + UNIQUE KEY `a` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (1),(2),(3); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t2`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t2` ( + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, + UNIQUE KEY `a` (`a`,`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t2` WRITE; +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t3`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t3` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t3` WRITE; +/*!40000 ALTER TABLE `t3` DISABLE KEYS */; +INSERT INTO `t3` VALUES (1,1),(2,2),(3,3); +ALTER TABLE `t3` ADD UNIQUE KEY `a` (`a`,`b`); +/*!40000 ALTER TABLE `t3` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t4`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t4` ( + `a` int(11) NOT NULL, + `b` int(11) NOT NULL, + PRIMARY KEY (`a`,`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t4` WRITE; +/*!40000 ALTER TABLE `t4` DISABLE KEYS */; +INSERT INTO `t4` VALUES (1,1),(2,2),(3,3); +ALTER TABLE `t4` ADD UNIQUE KEY `b` (`b`); +/*!40000 ALTER TABLE `t4` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +###################################### +DROP TABLE t1, t2, t3, t4; +CREATE TABLE t1 ( +id INT NOT NULL PRIMARY KEY +) ENGINE=InnoDB; +CREATE TABLE t2 ( +id INT NOT NULL AUTO_INCREMENT, +a INT NOT NULL, +PRIMARY KEY (id), +KEY (a), +FOREIGN KEY (a) REFERENCES t2 (id) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); +###################################### + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; +DROP TABLE IF EXISTS `t1`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t1` WRITE; +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; +INSERT INTO `t1` VALUES (1),(2),(3); +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; +UNLOCK TABLES; +DROP TABLE IF EXISTS `t2`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `t2` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `a` int(11) NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; +/*!40101 SET character_set_client = @saved_cs_client */; + +LOCK TABLES `t2` WRITE; +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; +INSERT INTO `t2` VALUES (1,1),(2,2),(3,3); +ALTER TABLE `t2` ADD KEY `a` (`a`); +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; +UNLOCK TABLES; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +###################################### +DROP TABLE t1, t2; --- /dev/null +++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test @@ -0,0 +1,188 @@ +# Embedded server doesn't support external clients +--source include/not_embedded.inc + +# Fast index creation is only available in InnoDB plugin +--source include/have_innodb.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo # +--echo # Test the --innodb-optimize-keys option. +--echo # + +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql + +# First test that the option has no effect on non-InnoDB tables + +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; + +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file + +--echo ###################################### +--cat_file $file +--echo ###################################### + +--remove_file $file + +DROP TABLE t1; + + +# Check that for InnoDB tables secondary keys are created after the data is +# dumped but foreign ones are left in CREATE TABLE + +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t2 VALUES (0), (1), (2); + +CREATE TABLE t1 ( + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + a INT, b VARCHAR(255), c DECIMAL(10,3), + KEY (b), + UNIQUE KEY uniq(c,a), + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE +) ENGINE=InnoDB; + +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); + +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file + +--echo ###################################### +--cat_file $file +--echo ###################################### + +# Check that the resulting dump can be imported back + +--exec $MYSQL test < $file + +--remove_file $file + +DROP TABLE t1, t2; + +######################################################################## +# Bug #812179: AUTO_INCREMENT columns must be skipped by the +# --innodb-optimize-keys optimization in mysqldump +######################################################################## + +CREATE TABLE t1 ( + id INT NOT NULL AUTO_INCREMENT, + KEY (id) +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + id INT NOT NULL AUTO_INCREMENT, + UNIQUE KEY (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (), (), (); +INSERT INTO t2 VALUES (), (), (); + +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file + +--echo ###################################### +--cat_file $file +--echo ###################################### + +# Check that the resulting dump can be imported back + +--exec $MYSQL test < $file + +--remove_file $file + +DROP TABLE t1, t2; + +######################################################################## +# Bug #851674: --innodb-optimize-keys does not work correctly with table +# without PRIMARY KEY +######################################################################## + +CREATE TABLE t1 ( + a INT NOT NULL, + UNIQUE KEY (a)) ENGINE=InnoDB; + +CREATE TABLE t2 ( + a INT NOT NULL, + b INT NOT NULL, + UNIQUE KEY (a,b)) ENGINE=InnoDB; + +CREATE TABLE t3 ( + a INT, + b INT, + UNIQUE KEY (a,b)) ENGINE=InnoDB; + +CREATE TABLE t4 ( + a INT NOT NULL, + b INT NOT NULL, + PRIMARY KEY (a,b), + UNIQUE KEY(b)) ENGINE=InnoDB; + +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE + TABLE_SCHEMA=DATABASE() AND + TABLE_NAME='t1' AND + COLUMN_KEY='PRI'; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE + TABLE_SCHEMA=DATABASE() AND + TABLE_NAME='t2' AND + COLUMN_KEY='PRI'; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE + TABLE_SCHEMA=DATABASE() AND + TABLE_NAME='t3' AND + COLUMN_KEY='PRI'; +SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE + TABLE_SCHEMA=DATABASE() AND + TABLE_NAME='t4' AND + COLUMN_KEY='PRI'; + +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1,1), (2,2), (3,3); +INSERT INTO t3 SELECT * FROM t2; +INSERT INTO t4 SELECT * FROM t2; + +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 t3 t4 >$file + +--echo ###################################### +--cat_file $file +--echo ###################################### + +# Check that the resulting dump can be imported back + +--exec $MYSQL test < $file + +--remove_file $file + +DROP TABLE t1, t2, t3, t4; + +######################################################################## +# Bug #859078: --innodb-optimize-keys should ignore foreign keys +######################################################################## + +CREATE TABLE t1 ( + id INT NOT NULL PRIMARY KEY +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + id INT NOT NULL AUTO_INCREMENT, + a INT NOT NULL, + PRIMARY KEY (id), + KEY (a), + FOREIGN KEY (a) REFERENCES t2 (id) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3); + +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file + +--echo ###################################### +--cat_file $file +--echo ###################################### + +# Check that the resulting dump can be imported back + +--exec $MYSQL test < $file + +--remove_file $file + +DROP TABLE t1, t2; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1638,6 +1638,9 @@ alter_list(rhs.alter_list, mem_root), key_list(rhs.key_list, mem_root), create_list(rhs.create_list, mem_root), + delayed_key_list(rhs.delayed_key_list, mem_root), + delayed_key_info(rhs.delayed_key_info), + delayed_key_count(rhs.delayed_key_count), flags(rhs.flags), keys_onoff(rhs.keys_onoff), tablespace_op(rhs.tablespace_op), @@ -1660,6 +1663,7 @@ list_copy_and_replace_each_value(alter_list, mem_root); list_copy_and_replace_each_value(key_list, mem_root); list_copy_and_replace_each_value(create_list, mem_root); + list_copy_and_replace_each_value(delayed_key_list, mem_root); /* partition_names are not deeply copied currently */ } --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1014,6 +1014,9 @@ List alter_list; List key_list; List create_list; + List delayed_key_list; + KEY *delayed_key_info; + uint delayed_key_count; uint flags; enum enum_enable_or_disable keys_onoff; enum tablespace_op_type tablespace_op; @@ -1025,6 +1028,8 @@ Alter_info() : + delayed_key_info(NULL), + delayed_key_count(0), flags(0), keys_onoff(LEAVE_AS_IS), tablespace_op(NO_TABLESPACE_OP), @@ -1040,6 +1045,9 @@ alter_list.empty(); key_list.empty(); create_list.empty(); + delayed_key_list.empty(); + delayed_key_info= NULL; + delayed_key_count= 0; flags= 0; keys_onoff= LEAVE_AS_IS; tablespace_op= NO_TABLESPACE_OP; --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -3220,6 +3220,14 @@ if (!*key_info_buffer || ! key_part_info) DBUG_RETURN(TRUE); // Out of memory + List_iterator delayed_key_iterator(alter_info->delayed_key_list); + alter_info->delayed_key_count= 0; + if (alter_info->delayed_key_list.elements > 0) + { + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) * + (*key_count)); + } + key_iterator.rewind(); key_number=0; for (; (key=key_iterator++) ; key_number++) @@ -3638,6 +3646,22 @@ key_info->comment.str= key->key_create_info.comment.str; } + if (alter_info->delayed_key_list.elements > 0) + { + Key *delayed_key; + + delayed_key_iterator.rewind(); + while ((delayed_key= delayed_key_iterator++)) + { + if (delayed_key == key) + { + alter_info->delayed_key_info[alter_info->delayed_key_count++]= + *key_info; + break; + } + } + } + key_info++; } if (!unique_key && !primary_key && @@ -5261,6 +5285,10 @@ List new_create_list; /* New key definitions are added here */ List new_key_list; + /* List with secondary keys which should be created after copying the data */ + List delayed_key_list; + /* Foreign key list returned by handler::get_foreign_key_list() */ + List f_key_list; List_iterator drop_it(alter_info->drop_list); List_iterator def_it(alter_info->create_list); List_iterator alter_it(alter_info->alter_list); @@ -5273,6 +5301,7 @@ uint used_fields= create_info->used_fields; KEY *key_info=table->key_info; bool rc= TRUE; + bool skip_secondary; DBUG_ENTER("mysql_prepare_alter_table"); @@ -5462,7 +5491,26 @@ /* Collect all keys which isn't in drop list. Add only those for which some fields exists. - */ + + We also store secondary keys in delayed_key_list to make use of + the InnoDB fast index creation. The following conditions must be + met: + + - fast_index_creation is enabled for the current session + - expand_fast_index_creation is enabled for the current session; + - we are going to create an InnoDB table (this is checked later when the + target engine is known); + - the key most be a non-UNIQUE one; + - there are no foreign keys. This can be optimized later to exclude only + those keys which are a part of foreign key constraints. Currently we + simply disable this optimization for all keys if there are any foreign + key constraints in the table. + */ + + skip_secondary= thd->variables.online_alter_index && + thd->variables.expand_fast_index_creation && + !table->file->get_foreign_key_list(thd, &f_key_list) && + f_key_list.elements == 0; for (uint i=0 ; i < table->s->keys ; i++,key_info++) { @@ -5579,6 +5627,8 @@ test(key_info->flags & HA_GENERATED_KEY), key_parts); new_key_list.push_back(key); + if (skip_secondary && key_type == Key::MULTIPLE) + delayed_key_list.push_back(key); } } { @@ -5586,7 +5636,21 @@ while ((key=key_it++)) // Add new keys { if (key->type != Key::FOREIGN_KEY) + { new_key_list.push_back(key); + if (skip_secondary && key->type == Key::MULTIPLE) + delayed_key_list.push_back(key); + } + else if (skip_secondary) + { + /* + We are adding a foreign key so disable the secondary keys + optimization. + */ + skip_secondary= FALSE; + delayed_key_list.empty(); + } + if (key->name.str && !my_strcasecmp(system_charset_info, key->name.str, primary_key_name)) { @@ -5635,12 +5699,104 @@ rc= FALSE; alter_info->create_list.swap(new_create_list); alter_info->key_list.swap(new_key_list); + alter_info->delayed_key_list.swap(delayed_key_list); err: DBUG_RETURN(rc); } /* + Temporarily remove secondary keys previously stored in + alter_info->delayed_key_info. +*/ +static int +remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) +{ + uint *key_numbers; + uint key_counter= 0; + uint i; + int error; + DBUG_ENTER("remove_secondary_keys"); + DBUG_ASSERT(alter_info->delayed_key_count > 0); + + key_numbers= (uint *) thd->alloc(sizeof(uint) * + alter_info->delayed_key_count); + for (i= 0; i < alter_info->delayed_key_count; i++) + { + KEY *key= alter_info->delayed_key_info + i; + uint j; + + for (j= 0; j < table->s->keys; j++) + { + if (!strcmp(table->key_info[j].name, key->name)) + { + key_numbers[key_counter++]= j; + break; + } + } + } + + DBUG_ASSERT(key_counter == alter_info->delayed_key_count); + + if ((error= table->file->prepare_drop_index(table, key_numbers, + key_counter)) || + (error= table->file->final_drop_index(table))) + { + table->file->print_error(error, MYF(0)); + } + + DBUG_RETURN(error); +} + +/* + Restore secondary keys previously removed in remove_secondary_keys. +*/ + +static int +restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) +{ + uint i; + int error; + DBUG_ENTER("restore_secondary_keys"); + DBUG_ASSERT(alter_info->delayed_key_count > 0); + + thd_proc_info(thd, "restoring secondary keys"); + + /* Fix the key parts */ + for (i= 0; i < alter_info->delayed_key_count; i++) + { + KEY *key = alter_info->delayed_key_info + i; + KEY_PART_INFO *key_part; + KEY_PART_INFO *part_end; + + part_end= key->key_part + key->key_parts; + for (key_part= key->key_part; key_part < part_end; key_part++) + key_part->field= table->field[key_part->fieldnr]; + } + handler_add_index *add; + if ((error= table->file->add_index(table, alter_info->delayed_key_info, + alter_info->delayed_key_count, &add))) + { + /* + Exchange the key_info for the error message. If we exchange + key number by key name in the message later, we need correct info. + */ + KEY *save_key_info= table->key_info; + table->key_info= alter_info->delayed_key_info; + table->file->print_error(error, MYF(0)); + table->key_info= save_key_info; + + DBUG_RETURN(error); + } + if ((error= table->file->final_add_index(add, true))) + { + table->file->print_error(error, MYF(0)); + } + + DBUG_RETURN(error); +} + +/* Alter table SYNOPSIS @@ -6434,19 +6590,38 @@ */ if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) { + /* + Check if we can temporarily remove secondary indexes from the table + before copying the data and recreate them later to utilize InnoDB fast + index creation. + TODO: is there a better way to check for InnoDB? + */ + bool optimize_keys= (alter_info->delayed_key_count > 0) && + !my_strcasecmp(system_charset_info, + new_table->file->table_type(), "InnoDB"); /* We don't want update TIMESTAMP fields during ALTER TABLE. */ new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; new_table->next_number_field=new_table->found_next_number_field; + thd_proc_info(thd, "copy to tmp table"); DBUG_EXECUTE_IF("abort_copy_table", { my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); goto err_new_table_cleanup; }); + + if (optimize_keys) + { + /* ignore the error */ + error= remove_secondary_keys(thd, new_table, alter_info); + } + error= copy_data_between_tables(table, new_table, alter_info->create_list, ignore, order_num, order, &copied, &deleted, alter_info->keys_onoff, alter_info->error_if_not_empty); + if (!error && optimize_keys) + error= restore_secondary_keys(thd, new_table, alter_info); } else { --- /dev/null +++ b/mysql-test/r/percona_innodb_expand_fast_index_creation.result @@ -0,0 +1,64 @@ +CREATE TABLE t1( +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, +a CHAR(1) NOT NULL, +b CHAR(36) NOT NULL) ENGINE=InnoDB; +INSERT INTO t1(a,b) VALUES ('a','b'); +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +ALTER TABLE t1 ADD KEY (a); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; +id 1 +select_type SIMPLE +table t1 +type ALL +possible_keys a +key NULL +key_len NULL +ref NULL +rows 16 +Extra +id 1 +select_type SIMPLE +table t2 +type ref +possible_keys a +key a +key_len 1 +ref test.t1.a +rows 1 +Extra Using where +ALTER TABLE t1 DROP KEY a; +SET expand_fast_index_creation = 1; +SELECT @@expand_fast_index_creation; +@@expand_fast_index_creation +1 +ALTER TABLE t1 ADD KEY (a); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; +id 1 +select_type SIMPLE +table t1 +type ALL +possible_keys a +key NULL +key_len NULL +ref NULL +rows 16 +Extra +id 1 +select_type SIMPLE +table t2 +type ALL +possible_keys a +key NULL +key_len NULL +ref NULL +rows 16 +Extra Using where; Using join buffer +SET expand_fast_index_creation = 0; +DROP TABLE t1; --- /dev/null +++ b/mysql-test/t/percona_innodb_expand_fast_index_creation.test @@ -0,0 +1,45 @@ +--source include/have_innodb.inc + +######################################################################## +# Bug #857590: Fast index creation does not update index statistics +######################################################################## + +CREATE TABLE t1( + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, + a CHAR(1) NOT NULL, + b CHAR(36) NOT NULL) ENGINE=InnoDB; + +INSERT INTO t1(a,b) VALUES ('a','b'); +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; +INSERT INTO t1(a,b) SELECT a,b FROM t1; + +# Check that fast index creation is used +--enable_info +ALTER TABLE t1 ADD KEY (a); +--disable_info + +# The default (wrong) plan due to bogus statistics +--vertical_results +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; +--horizontal_results + +ALTER TABLE t1 DROP KEY a; + +SET expand_fast_index_creation = 1; +SELECT @@expand_fast_index_creation; + +# Check that stats are updated with the option enabled + +--enable_info +ALTER TABLE t1 ADD KEY (a); +--disable_info + +--vertical_results +EXPLAIN SELECT COUNT(*) FROM t1, t1 t2 WHERE t1.a = t2.a AND t1.b = t2.b; +--horizontal_results + +SET expand_fast_index_creation = 0; + +DROP TABLE t1; --- a/storage/innobase/row/row0merge.c +++ b/storage/innobase/row/row0merge.c @@ -56,6 +56,7 @@ #include "log0log.h" #include "ut0sort.h" #include "handler0alter.h" +#include "ha_prototypes.h" /* Ignore posix_fadvise() on those platforms where it does not exist */ #if defined __WIN__ @@ -2673,6 +2674,9 @@ } } + if (trx->mysql_thd && thd_expand_fast_index_creation(trx->mysql_thd)) + dict_update_statistics(new_table, FALSE, TRUE); + func_exit: row_merge_file_destroy_low(tmpfd); --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -551,6 +551,7 @@ double long_query_time_double; + my_bool expand_fast_index_creation; } SV; --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -783,6 +783,14 @@ ON_CHECK(event_scheduler_check), ON_UPDATE(event_scheduler_update)); #endif +static Sys_var_mybool Sys_expand_fast_index_creation( + "expand_fast_index_creation", + "Enable/disable improvements to the InnoDB fast index creation " + "functionality. Has no effect when fast index creation is disabled with " + "the fast-index-creation option", + SESSION_VAR(expand_fast_index_creation), CMD_LINE(OPT_ARG), + DEFAULT(FALSE)); + static Sys_var_ulong Sys_expire_logs_days( "expire_logs_days", "If non-zero, binary logs will be purged after expire_logs_days " --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -999,6 +999,19 @@ return(THDVAR((THD*) thd, flush_log_at_trx_commit)); } +/******************************************************************//** +Returns true if expand_fast_index_creation is enabled for the current +session. +@return the value of the server's expand_fast_index_creation variable */ +extern "C" UNIV_INTERN +ibool +thd_expand_fast_index_creation( +/*================================*/ + void* thd) +{ + return((ibool) (((THD*) thd)->variables.expand_fast_index_creation)); +} + /********************************************************************//** Obtain the InnoDB transaction of a MySQL thread. @return reference to transaction pointer */ --- a/storage/innobase/include/ha_prototypes.h +++ b/storage/innobase/include/ha_prototypes.h @@ -303,4 +303,15 @@ innobase_get_lower_case_table_names(void); /*=====================================*/ +/******************************************************************//** +Returns true if innodb_expand_fast_index_creation is enabled for the current +session. +@return the value of the server's innodb_expand_fast_index_creation variable */ + +ibool +thd_expand_fast_index_creation( +/*==================*/ + void* thd); /*!< in: thread handle (THD*) */ + + #endif --- /dev/null +++ b/mysql-test/suite/sys_vars/r/expand_fast_index_creation_basic.result @@ -0,0 +1,6 @@ +SELECT @@global.expand_fast_index_creation; +@@global.expand_fast_index_creation +0 +SELECT @@local.expand_fast_index_creation; +@@local.expand_fast_index_creation +0 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/expand_fast_index_creation_basic.test @@ -0,0 +1,2 @@ +SELECT @@global.expand_fast_index_creation; +SELECT @@local.expand_fast_index_creation; --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -140,6 +140,10 @@ and DISABLED (keep the event scheduler completely deactivated, it cannot be activated run-time) -T, --exit-info[=#] Used for debugging. Use at your own risk. + --expand-fast-index-creation + Enable/disable improvements to the InnoDB fast index + creation functionality. Has no effect when fast index + creation is disabled with the fast-index-creation option --expire-logs-days=# If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup @@ -823,6 +827,7 @@ div-precision-increment 4 engine-condition-pushdown TRUE event-scheduler OFF +expand-fast-index-creation FALSE expire-logs-days 0 external-locking FALSE flush FALSE --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -140,6 +140,10 @@ and DISABLED (keep the event scheduler completely deactivated, it cannot be activated run-time) -T, --exit-info[=#] Used for debugging. Use at your own risk. + --expand-fast-index-creation + Enable/disable improvements to InnoDB fast index creation + functionality. Has no effect when fast index creation is + disabled with the fast-index-creation option --expire-logs-days=# If non-zero, binary logs will be purged after expire_logs_days days; possible purges happen at startup @@ -781,6 +785,7 @@ div-precision-increment 4 engine-condition-pushdown TRUE event-scheduler OFF +expand-fast-index-creation FALSE expire-logs-days 0 external-locking FALSE flush FALSE