]>
Commit | Line | Data |
---|---|---|
11822e22 AM |
1 | # name : innodb_expand_fast_index_creation.patch |
2 | # maintainer : Alexey | |
3 | # | |
4 | # Expands the applicability of InnoDB fast index creation to mysqldump, | |
5 | # ALTER TABLE and OPTIMIZE TABLE. | |
6 | # | |
adf0fb13 | 7 | # |
11822e22 AM |
8 | diff -ruN a/client/client_priv.h b/client/client_priv.h |
9 | --- a/client/client_priv.h 2011-04-11 08:57:20.000000000 +0400 | |
10 | +++ b/client/client_priv.h 2011-04-11 08:57:21.000000000 +0400 | |
adf0fb13 | 11 | @@ -90,6 +90,7 @@ |
11822e22 AM |
12 | OPT_NO_REMOVE_EOL_CARRET, |
13 | OPT_DEFAULT_AUTH, | |
14 | OPT_DEFAULT_PLUGIN, | |
15 | + OPT_INNODB_OPTIMIZE_KEYS, | |
16 | OPT_MAX_CLIENT_OPTION | |
17 | }; | |
18 | ||
19 | diff -ruN a/client/mysqldump.c b/client/mysqldump.c | |
20 | --- a/client/mysqldump.c 2011-04-11 08:57:17.000000000 +0400 | |
21 | +++ b/client/mysqldump.c 2011-04-11 08:57:21.000000000 +0400 | |
22 | @@ -45,6 +45,7 @@ | |
23 | #include <m_ctype.h> | |
24 | #include <hash.h> | |
25 | #include <stdarg.h> | |
26 | +#include <my_list.h> | |
27 | ||
28 | #include "client_priv.h" | |
29 | #include "mysql.h" | |
30 | @@ -141,6 +142,8 @@ | |
31 | ||
32 | static my_bool server_supports_sql_no_fcache= FALSE; | |
33 | ||
34 | +static my_bool opt_innodb_optimize_keys= FALSE; | |
35 | + | |
36 | /* | |
37 | Dynamic_string wrapper functions. In this file use these | |
38 | wrappers, they will terminate the process if there is | |
39 | @@ -186,6 +189,8 @@ | |
40 | ||
41 | HASH ignore_table; | |
42 | ||
43 | +LIST *skipped_keys_list; | |
44 | + | |
45 | static struct my_option my_long_options[] = | |
46 | { | |
47 | {"all-databases", 'A', | |
48 | @@ -349,6 +354,11 @@ | |
49 | "in dump produced with --dump-slave.", &opt_include_master_host_port, | |
50 | &opt_include_master_host_port, 0, GET_BOOL, NO_ARG, | |
51 | 0, 0, 0, 0, 0, 0}, | |
52 | + {"innodb-optimize-keys", OPT_INNODB_OPTIMIZE_KEYS, | |
53 | + "Use InnoDB fast index creation by creating secondary indexes after " | |
54 | + "dumping the data.", | |
55 | + &opt_innodb_optimize_keys, &opt_innodb_optimize_keys, 0, GET_BOOL, NO_ARG, | |
56 | + 0, 0, 0, 0, 0, 0}, | |
57 | {"insert-ignore", OPT_INSERT_IGNORE, "Insert rows with INSERT IGNORE.", | |
58 | &opt_ignore, &opt_ignore, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, | |
59 | 0, 0}, | |
60 | @@ -2236,6 +2246,77 @@ | |
61 | } | |
62 | ||
63 | /* | |
64 | + Remove secondary/foreign key definitions from a given SHOW CREATE TABLE string | |
65 | + and store them into a temporary list to be used later. | |
66 | + | |
67 | + SYNOPSIS | |
68 | + skip_secondary_keys() | |
69 | + create_str SHOW CREATE TABLE output | |
70 | + | |
71 | + | |
72 | + DESCRIPTION | |
73 | + | |
74 | + Stores all lines starting with "KEY" or "UNIQUE KEY" or "CONSTRAINT" | |
75 | + into skipped_keys_list and removes them from the input string. | |
76 | + Ignoring FOREIGN KEYS constraints when creating the table is ok, because | |
77 | + mysqldump sets foreign_key_checks to 0 anyway. | |
78 | +*/ | |
79 | + | |
80 | +static void skip_secondary_keys(char *create_str) | |
81 | +{ | |
82 | + char *ptr, *strend; | |
83 | + char *last_comma = NULL; | |
84 | + | |
85 | + strend= create_str + strlen(create_str); | |
86 | + | |
87 | + ptr= create_str; | |
88 | + while (*ptr) | |
89 | + { | |
90 | + char *tmp, *orig_ptr; | |
91 | + | |
92 | + orig_ptr= ptr; | |
93 | + /* Skip leading whitespace */ | |
94 | + while (*ptr && my_isspace(charset_info, *ptr)) | |
95 | + ptr++; | |
96 | + | |
97 | + /* Read the next line */ | |
98 | + for (tmp= ptr; *tmp != '\n' && *tmp != '\0'; tmp++); | |
99 | + | |
100 | + /* Is it a secondary index definition? */ | |
101 | + if (*tmp == '\n' && | |
102 | + (!strncmp(ptr, "UNIQUE KEY ", sizeof("UNIQUE KEY ") - 1) || | |
103 | + !strncmp(ptr, "KEY ", sizeof("KEY ") - 1) || | |
104 | + !strncmp(ptr, "CONSTRAINT ", sizeof("CONSTRAINT ") - 1))) | |
105 | + { | |
106 | + char *data, *end= tmp - 1; | |
107 | + | |
108 | + /* Remove the trailing comma */ | |
109 | + if (*end == ',') | |
110 | + end--; | |
111 | + data= my_strndup(ptr, end - ptr + 1, MYF(MY_FAE)); | |
112 | + skipped_keys_list= list_cons(data, skipped_keys_list); | |
113 | + | |
114 | + memmove(orig_ptr, tmp + 1, strend - tmp); | |
115 | + ptr= orig_ptr; | |
116 | + strend-= tmp + 1 - ptr; | |
117 | + | |
118 | + /* Remove the comma on the previos line */ | |
119 | + if (last_comma != NULL) | |
120 | + { | |
121 | + *last_comma= ' '; | |
122 | + last_comma = NULL; | |
123 | + } | |
124 | + } | |
125 | + else | |
126 | + { | |
127 | + if (tmp[-1] == ',') | |
128 | + last_comma= tmp - 1; | |
129 | + ptr= (*tmp == '\0') ? tmp : tmp + 1; | |
130 | + } | |
131 | + } | |
132 | +} | |
133 | + | |
134 | +/* | |
135 | get_table_structure -- retrievs database structure, prints out corresponding | |
136 | CREATE statement and fills out insert_pat if the table is the type we will | |
137 | be dumping. | |
138 | @@ -2476,6 +2557,9 @@ | |
139 | ||
140 | row= mysql_fetch_row(result); | |
141 | ||
142 | + if (opt_innodb_optimize_keys && !strcmp(table_type, "InnoDB")) | |
143 | + skip_secondary_keys(row[1]); | |
144 | + | |
145 | fprintf(sql_file, (opt_compatible_mode & 3) ? "%s;\n" : | |
146 | "/*!40101 SET @saved_cs_client = @@character_set_client */;\n" | |
147 | "/*!40101 SET character_set_client = utf8 */;\n" | |
148 | @@ -3570,6 +3654,27 @@ | |
149 | goto err; | |
150 | } | |
151 | ||
152 | + /* Perform delayed secondary index creation for --innodb-optimize-keys */ | |
153 | + if (skipped_keys_list) | |
154 | + { | |
155 | + uint keys; | |
156 | + skipped_keys_list= list_reverse(skipped_keys_list); | |
157 | + fprintf(md_result_file, "ALTER TABLE %s ", opt_quoted_table); | |
158 | + for (keys= list_length(skipped_keys_list); keys > 0; keys--) | |
159 | + { | |
160 | + LIST *node= skipped_keys_list; | |
161 | + char *def= node->data; | |
162 | + | |
163 | + fprintf(md_result_file, "ADD %s%s", def, (keys > 1) ? ", " : ";\n"); | |
164 | + | |
165 | + skipped_keys_list= list_delete(skipped_keys_list, node); | |
166 | + my_free(def); | |
167 | + my_free(node); | |
168 | + } | |
169 | + | |
170 | + DBUG_ASSERT(skipped_keys_list == NULL); | |
171 | + } | |
172 | + | |
173 | /* Moved enable keys to before unlock per bug 15977 */ | |
174 | if (opt_disable_keys) | |
175 | { | |
176 | diff -ruN /dev/null b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result | |
177 | --- /dev/null 1970-01-01 00:00:00.000000000 +0000 | |
178 | +++ b/mysql-test/r/percona_mysqldump_innodb_optimize_keys.result 2011-04-11 08:57:21.000000000 +0400 | |
179 | @@ -0,0 +1,109 @@ | |
180 | +# | |
181 | +# Test the --innodb-optimize-keys option. | |
182 | +# | |
183 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; | |
184 | +###################################### | |
185 | + | |
186 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; | |
187 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; | |
188 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; | |
189 | +/*!40101 SET NAMES utf8 */; | |
190 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; | |
191 | +/*!40103 SET TIME_ZONE='+00:00' */; | |
192 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; | |
193 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; | |
194 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; | |
195 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; | |
196 | +DROP TABLE IF EXISTS `t1`; | |
197 | +/*!40101 SET @saved_cs_client = @@character_set_client */; | |
198 | +/*!40101 SET character_set_client = utf8 */; | |
199 | +CREATE TABLE `t1` ( | |
200 | + `a` int(11) NOT NULL, | |
201 | + `b` int(11) DEFAULT NULL, | |
202 | + PRIMARY KEY (`a`), | |
203 | + KEY `b` (`b`) | |
204 | +) ENGINE=MyISAM DEFAULT CHARSET=latin1; | |
205 | +/*!40101 SET character_set_client = @saved_cs_client */; | |
206 | + | |
207 | +LOCK TABLES `t1` WRITE; | |
208 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; | |
209 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; | |
210 | +UNLOCK TABLES; | |
211 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; | |
212 | + | |
213 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; | |
214 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; | |
215 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; | |
216 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; | |
217 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; | |
218 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; | |
219 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; | |
220 | + | |
221 | +###################################### | |
222 | +DROP TABLE t1; | |
223 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; | |
224 | +INSERT INTO t2 VALUES (0), (1), (2); | |
225 | +CREATE TABLE t1 ( | |
226 | +id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
227 | +a INT, b VARCHAR(255), c DECIMAL(10,3), | |
228 | +KEY (b), | |
229 | +UNIQUE KEY uniq(c,a), | |
230 | +FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE | |
231 | +) ENGINE=InnoDB; | |
232 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); | |
233 | +###################################### | |
234 | + | |
235 | +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; | |
236 | +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; | |
237 | +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; | |
238 | +/*!40101 SET NAMES utf8 */; | |
239 | +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; | |
240 | +/*!40103 SET TIME_ZONE='+00:00' */; | |
241 | +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; | |
242 | +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; | |
243 | +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; | |
244 | +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; | |
245 | +DROP TABLE IF EXISTS `t1`; | |
246 | +/*!40101 SET @saved_cs_client = @@character_set_client */; | |
247 | +/*!40101 SET character_set_client = utf8 */; | |
248 | +CREATE TABLE `t1` ( | |
249 | + `id` int(11) NOT NULL AUTO_INCREMENT, | |
250 | + `a` int(11) DEFAULT NULL, | |
251 | + `b` varchar(255) DEFAULT NULL, | |
252 | + `c` decimal(10,3) DEFAULT NULL, | |
253 | + PRIMARY KEY (`id`) | |
254 | +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; | |
255 | +/*!40101 SET character_set_client = @saved_cs_client */; | |
256 | + | |
257 | +LOCK TABLES `t1` WRITE; | |
258 | +/*!40000 ALTER TABLE `t1` DISABLE KEYS */; | |
259 | +INSERT INTO `t1` VALUES (1,0,'0',0.000),(2,1,'1',1.100),(3,2,'2',2.200); | |
260 | +ALTER TABLE `t1` ADD UNIQUE KEY `uniq` (`c`,`a`), ADD KEY `b` (`b`), ADD KEY `a` (`a`), ADD CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`) ON DELETE CASCADE; | |
261 | +/*!40000 ALTER TABLE `t1` ENABLE KEYS */; | |
262 | +UNLOCK TABLES; | |
263 | +DROP TABLE IF EXISTS `t2`; | |
264 | +/*!40101 SET @saved_cs_client = @@character_set_client */; | |
265 | +/*!40101 SET character_set_client = utf8 */; | |
266 | +CREATE TABLE `t2` ( | |
267 | + `a` int(11) NOT NULL, | |
268 | + PRIMARY KEY (`a`) | |
269 | +) ENGINE=InnoDB DEFAULT CHARSET=latin1; | |
270 | +/*!40101 SET character_set_client = @saved_cs_client */; | |
271 | + | |
272 | +LOCK TABLES `t2` WRITE; | |
273 | +/*!40000 ALTER TABLE `t2` DISABLE KEYS */; | |
274 | +INSERT INTO `t2` VALUES (0),(1),(2); | |
275 | +/*!40000 ALTER TABLE `t2` ENABLE KEYS */; | |
276 | +UNLOCK TABLES; | |
277 | +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; | |
278 | + | |
279 | +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; | |
280 | +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; | |
281 | +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; | |
282 | +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; | |
283 | +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; | |
284 | +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; | |
285 | +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; | |
286 | + | |
287 | +###################################### | |
288 | +DROP TABLE t1, t2; | |
289 | diff -ruN a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result | |
290 | --- a/mysql-test/suite/innodb/r/innodb.result 2011-03-31 17:36:17.000000000 +0400 | |
291 | +++ b/mysql-test/suite/innodb/r/innodb.result 2011-04-11 23:26:45.000000000 +0400 | |
292 | @@ -1673,7 +1673,7 @@ | |
293 | 71 | |
294 | SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; | |
295 | variable_value - @innodb_rows_inserted_orig | |
296 | -1066 | |
297 | +1108 | |
298 | SELECT variable_value - @innodb_rows_updated_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_updated'; | |
299 | variable_value - @innodb_rows_updated_orig | |
300 | 866 | |
301 | diff -ruN a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test | |
302 | --- a/mysql-test/suite/innodb/t/innodb-index.test 2011-03-31 17:36:17.000000000 +0400 | |
303 | +++ b/mysql-test/suite/innodb/t/innodb-index.test 2011-04-11 08:57:21.000000000 +0400 | |
304 | @@ -28,6 +28,11 @@ | |
305 | show create table t1; | |
306 | --error ER_MULTIPLE_PRI_KEY | |
307 | alter table t1 add primary key (c); | |
308 | +# Suppress the error log messages occuring on duplicate key error | |
309 | +# during ALTER TABLE when using fast index creation | |
310 | +--disable_query_log | |
311 | +call mtr.add_suppression("Cannot find index PRIMARY in InnoDB index translation table."); | |
312 | +--enable_query_log | |
313 | --error ER_DUP_ENTRY | |
314 | alter table t1 drop primary key, add primary key (b); | |
315 | create unique index c on t1 (c); | |
316 | diff -ruN a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test | |
317 | --- a/mysql-test/suite/innodb/t/innodb.test 2011-03-31 17:36:17.000000000 +0400 | |
318 | +++ b/mysql-test/suite/innodb/t/innodb.test 2011-04-11 08:57:21.000000000 +0400 | |
319 | @@ -21,6 +21,12 @@ | |
320 | ||
321 | -- source include/have_innodb.inc | |
322 | ||
323 | +# Suppress the error log message occuring on duplicate key error | |
324 | +# during ALTER TABLE when using fast index creation | |
325 | +--disable_query_log | |
326 | +call mtr.add_suppression("Cannot find index v_2 in InnoDB index translation table."); | |
327 | +--enable_query_log | |
328 | + | |
329 | let $MYSQLD_DATADIR= `select @@datadir`; | |
330 | ||
331 | # Save the original values of some variables in order to be able to | |
332 | diff -ruN /dev/null b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test | |
333 | --- /dev/null 1970-01-01 00:00:00.000000000 +0000 | |
334 | +++ b/mysql-test/t/percona_mysqldump_innodb_optimize_keys.test 2011-04-11 08:57:21.000000000 +0400 | |
335 | @@ -0,0 +1,62 @@ | |
336 | +# Embedded server doesn't support external clients | |
337 | +--source include/not_embedded.inc | |
338 | + | |
339 | +# Fast index creation is only available in InnoDB plugin | |
340 | +--source include/have_innodb.inc | |
341 | + | |
342 | +# Save the initial number of concurrent sessions | |
343 | +--source include/count_sessions.inc | |
344 | + | |
345 | +--echo # | |
346 | +--echo # Test the --innodb-optimize-keys option. | |
347 | +--echo # | |
348 | + | |
349 | +--let $file=$MYSQLTEST_VARDIR/tmp/t1.sql | |
350 | + | |
351 | +# First test that the option has no effect on non-InnoDB tables | |
352 | + | |
353 | +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, KEY(b)) ENGINE=MyISAM; | |
354 | + | |
355 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 >$file | |
356 | + | |
357 | +--echo ###################################### | |
358 | +--cat_file $file | |
359 | +--echo ###################################### | |
360 | + | |
361 | +--remove_file $file | |
362 | + | |
363 | +DROP TABLE t1; | |
364 | + | |
365 | + | |
366 | +# Check that for InnoDB tables secondary and foreign keys are created | |
367 | +# after the data is dumped | |
368 | + | |
369 | +CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; | |
370 | +INSERT INTO t2 VALUES (0), (1), (2); | |
371 | + | |
372 | +CREATE TABLE t1 ( | |
373 | + id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, | |
374 | + a INT, b VARCHAR(255), c DECIMAL(10,3), | |
375 | + KEY (b), | |
376 | + UNIQUE KEY uniq(c,a), | |
377 | + FOREIGN KEY (a) REFERENCES t2(a) ON DELETE CASCADE | |
378 | +) ENGINE=InnoDB; | |
379 | + | |
380 | +INSERT INTO t1(a,b,c) VALUES (0, "0", 0.0), (1, "1", 1.1), (2, "2", 2.2); | |
381 | + | |
382 | +--exec $MYSQL_DUMP --skip-comments --innodb-optimize-keys test t1 t2 >$file | |
383 | + | |
384 | +--echo ###################################### | |
385 | +--cat_file $file | |
386 | +--echo ###################################### | |
387 | + | |
388 | +# Check that the resulting dump can be imported back | |
389 | + | |
390 | +--exec $MYSQL test < $file | |
391 | + | |
392 | +--remove_file $file | |
393 | + | |
394 | +DROP TABLE t1, t2; | |
395 | + | |
396 | +# Wait till we reached the initial number of concurrent sessions | |
397 | +--source include/wait_until_count_sessions.inc | |
398 | diff -ruN a/sql/sql_lex.cc b/sql/sql_lex.cc | |
399 | --- a/sql/sql_lex.cc 2011-04-11 08:57:17.000000000 +0400 | |
400 | +++ b/sql/sql_lex.cc 2011-04-11 08:57:21.000000000 +0400 | |
401 | @@ -1630,6 +1630,9 @@ | |
402 | alter_list(rhs.alter_list, mem_root), | |
403 | key_list(rhs.key_list, mem_root), | |
404 | create_list(rhs.create_list, mem_root), | |
405 | + delayed_key_list(rhs.delayed_key_list, mem_root), | |
406 | + delayed_key_info(rhs.delayed_key_info), | |
407 | + delayed_key_count(rhs.delayed_key_count), | |
408 | flags(rhs.flags), | |
409 | keys_onoff(rhs.keys_onoff), | |
410 | tablespace_op(rhs.tablespace_op), | |
411 | @@ -1652,6 +1655,7 @@ | |
412 | list_copy_and_replace_each_value(alter_list, mem_root); | |
413 | list_copy_and_replace_each_value(key_list, mem_root); | |
414 | list_copy_and_replace_each_value(create_list, mem_root); | |
415 | + list_copy_and_replace_each_value(delayed_key_list, mem_root); | |
416 | /* partition_names are not deeply copied currently */ | |
417 | } | |
418 | ||
419 | diff -ruN a/sql/sql_lex.h b/sql/sql_lex.h | |
420 | --- a/sql/sql_lex.h 2011-04-11 08:57:19.000000000 +0400 | |
421 | +++ b/sql/sql_lex.h 2011-04-11 08:57:21.000000000 +0400 | |
422 | @@ -1003,6 +1003,9 @@ | |
423 | List<Alter_column> alter_list; | |
424 | List<Key> key_list; | |
425 | List<Create_field> create_list; | |
426 | + List<Key> delayed_key_list; | |
427 | + KEY *delayed_key_info; | |
428 | + uint delayed_key_count; | |
429 | uint flags; | |
430 | enum enum_enable_or_disable keys_onoff; | |
431 | enum tablespace_op_type tablespace_op; | |
432 | @@ -1014,6 +1017,8 @@ | |
433 | ||
434 | ||
435 | Alter_info() : | |
436 | + delayed_key_info(NULL), | |
437 | + delayed_key_count(0), | |
438 | flags(0), | |
439 | keys_onoff(LEAVE_AS_IS), | |
440 | tablespace_op(NO_TABLESPACE_OP), | |
441 | @@ -1029,6 +1034,9 @@ | |
442 | alter_list.empty(); | |
443 | key_list.empty(); | |
444 | create_list.empty(); | |
445 | + delayed_key_list.empty(); | |
446 | + delayed_key_info= NULL; | |
447 | + delayed_key_count= 0; | |
448 | flags= 0; | |
449 | keys_onoff= LEAVE_AS_IS; | |
450 | tablespace_op= NO_TABLESPACE_OP; | |
451 | diff -ruN a/sql/sql_table.cc b/sql/sql_table.cc | |
452 | --- a/sql/sql_table.cc 2011-04-11 08:56:57.000000000 +0400 | |
453 | +++ b/sql/sql_table.cc 2011-04-11 23:30:02.000000000 +0400 | |
454 | @@ -2773,7 +2773,7 @@ | |
455 | file The handler for the new table. | |
456 | key_info_buffer OUT An array of KEY structs for the indexes. | |
457 | key_count OUT The number of elements in the array. | |
458 | - select_field_count The number of fields coming from a select table. | |
459 | + select_field_count The number of fields coming from a select table. | |
460 | ||
461 | DESCRIPTION | |
462 | Prepares the table and key structures for table creation. | |
463 | @@ -3119,7 +3119,6 @@ | |
464 | } | |
465 | ||
466 | /* Create keys */ | |
467 | - | |
468 | List_iterator<Key> key_iterator(alter_info->key_list); | |
469 | List_iterator<Key> key_iterator2(alter_info->key_list); | |
470 | uint key_parts=0, fk_key_count=0; | |
471 | @@ -3217,6 +3216,14 @@ | |
472 | if (!*key_info_buffer || ! key_part_info) | |
473 | DBUG_RETURN(TRUE); // Out of memory | |
474 | ||
475 | + List_iterator<Key> delayed_key_iterator(alter_info->delayed_key_list); | |
476 | + alter_info->delayed_key_count= 0; | |
477 | + if (alter_info->delayed_key_list.elements > 0) | |
478 | + { | |
479 | + alter_info->delayed_key_info= (KEY *) sql_calloc(sizeof(KEY) * | |
480 | + (*key_count)); | |
481 | + } | |
482 | + | |
483 | key_iterator.rewind(); | |
484 | key_number=0; | |
485 | for (; (key=key_iterator++) ; key_number++) | |
486 | @@ -3635,6 +3642,22 @@ | |
487 | key_info->comment.str= key->key_create_info.comment.str; | |
488 | } | |
489 | ||
490 | + if (alter_info->delayed_key_list.elements > 0) | |
491 | + { | |
492 | + Key *delayed_key; | |
493 | + | |
494 | + delayed_key_iterator.rewind(); | |
495 | + while ((delayed_key= delayed_key_iterator++)) | |
496 | + { | |
497 | + if (delayed_key == key) | |
498 | + { | |
499 | + alter_info->delayed_key_info[alter_info->delayed_key_count++]= | |
500 | + *key_info; | |
501 | + break; | |
502 | + } | |
503 | + } | |
504 | + } | |
505 | + | |
506 | key_info++; | |
507 | } | |
508 | if (!unique_key && !primary_key && | |
509 | @@ -5247,6 +5270,10 @@ | |
510 | List<Create_field> new_create_list; | |
511 | /* New key definitions are added here */ | |
512 | List<Key> new_key_list; | |
513 | + /* List with secondary keys which should be created after copying the data */ | |
514 | + List<Key> delayed_key_list; | |
515 | + /* Foreign key list returned by handler::get_foreign_key_list() */ | |
516 | + List<FOREIGN_KEY_INFO> f_key_list; | |
517 | List_iterator<Alter_drop> drop_it(alter_info->drop_list); | |
518 | List_iterator<Create_field> def_it(alter_info->create_list); | |
519 | List_iterator<Alter_column> alter_it(alter_info->alter_list); | |
520 | @@ -5259,6 +5286,7 @@ | |
521 | uint used_fields= create_info->used_fields; | |
522 | KEY *key_info=table->key_info; | |
523 | bool rc= TRUE; | |
524 | + bool skip_secondary; | |
525 | ||
526 | DBUG_ENTER("mysql_prepare_alter_table"); | |
527 | ||
adf0fb13 | 528 | @@ -5431,7 +5459,23 @@ |
11822e22 AM |
529 | /* |
530 | Collect all keys which isn't in drop list. Add only those | |
531 | for which some fields exists. | |
532 | - */ | |
533 | + | |
534 | + We also store secondary keys in delayed_key_list to make use of | |
535 | + the InnoDB fast index creation. The following conditions must be | |
536 | + met: | |
537 | + | |
538 | + - we are going to create an InnoDB table (this is checked later when the | |
539 | + target engine is known); | |
540 | + - the key most be a non-UNIQUE one; | |
541 | + - there are no foreign keys. This can be optimized later to exclude only | |
542 | + those keys which are a part of foreign key constraints. Currently we | |
543 | + simply disable this optimization for all keys if there are any foreign | |
544 | + key constraints in the table. | |
545 | + */ | |
546 | + | |
547 | + skip_secondary= | |
548 | + !table->file->get_foreign_key_list(thd, &f_key_list) && | |
549 | + f_key_list.elements == 0; | |
550 | ||
551 | for (uint i=0 ; i < table->s->keys ; i++,key_info++) | |
552 | { | |
adf0fb13 | 553 | @@ -5548,6 +5592,8 @@ |
11822e22 AM |
554 | test(key_info->flags & HA_GENERATED_KEY), |
555 | key_parts); | |
556 | new_key_list.push_back(key); | |
557 | + if (skip_secondary && key_type == Key::MULTIPLE) | |
558 | + delayed_key_list.push_back(key); | |
559 | } | |
560 | } | |
561 | { | |
adf0fb13 | 562 | @@ -5555,7 +5601,21 @@ |
11822e22 AM |
563 | while ((key=key_it++)) // Add new keys |
564 | { | |
565 | if (key->type != Key::FOREIGN_KEY) | |
566 | + { | |
567 | new_key_list.push_back(key); | |
568 | + if (skip_secondary && key->type == Key::MULTIPLE) | |
569 | + delayed_key_list.push_back(key); | |
570 | + } | |
571 | + else if (skip_secondary) | |
572 | + { | |
573 | + /* | |
574 | + We are adding a foreign key so disable the secondary keys | |
575 | + optimization. | |
576 | + */ | |
577 | + skip_secondary= FALSE; | |
578 | + delayed_key_list.empty(); | |
579 | + } | |
580 | + | |
581 | if (key->name.str && | |
582 | !my_strcasecmp(system_charset_info, key->name.str, primary_key_name)) | |
583 | { | |
adf0fb13 | 584 | @@ -5604,12 +5664,100 @@ |
11822e22 AM |
585 | rc= FALSE; |
586 | alter_info->create_list.swap(new_create_list); | |
587 | alter_info->key_list.swap(new_key_list); | |
588 | + alter_info->delayed_key_list.swap(delayed_key_list); | |
589 | err: | |
590 | DBUG_RETURN(rc); | |
591 | } | |
592 | ||
593 | ||
594 | /* | |
595 | + Temporarily remove secondary keys previously stored in | |
596 | + alter_info->delayed_key_info. | |
597 | +*/ | |
598 | +static int | |
599 | +remove_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) | |
600 | +{ | |
601 | + uint *key_numbers; | |
602 | + uint key_counter= 0; | |
603 | + uint i; | |
604 | + int error; | |
605 | + DBUG_ENTER("remove_secondary_keys"); | |
606 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); | |
607 | + | |
608 | + key_numbers= (uint *) thd->alloc(sizeof(uint) * | |
609 | + alter_info->delayed_key_count); | |
610 | + for (i= 0; i < alter_info->delayed_key_count; i++) | |
611 | + { | |
612 | + KEY *key= alter_info->delayed_key_info + i; | |
613 | + uint j; | |
614 | + | |
615 | + for (j= 0; j < table->s->keys; j++) | |
616 | + { | |
617 | + if (!strcmp(table->key_info[j].name, key->name)) | |
618 | + { | |
619 | + key_numbers[key_counter++]= j; | |
620 | + break; | |
621 | + } | |
622 | + } | |
623 | + } | |
624 | + | |
625 | + DBUG_ASSERT(key_counter == alter_info->delayed_key_count); | |
626 | + | |
627 | + if ((error= table->file->prepare_drop_index(table, key_numbers, | |
628 | + key_counter)) || | |
629 | + (error= table->file->final_drop_index(table))) | |
630 | + { | |
631 | + table->file->print_error(error, MYF(0)); | |
632 | + } | |
633 | + | |
634 | + DBUG_RETURN(error); | |
635 | +} | |
636 | + | |
637 | +/* | |
638 | + Restore secondary keys previously removed in remove_secondary_keys. | |
639 | +*/ | |
640 | + | |
641 | +static int | |
642 | +restore_secondary_keys(THD *thd, TABLE *table, Alter_info *alter_info) | |
643 | +{ | |
644 | + uint i; | |
645 | + int error; | |
646 | + DBUG_ENTER("restore_secondary_keys"); | |
647 | + DBUG_ASSERT(alter_info->delayed_key_count > 0); | |
648 | + | |
649 | + thd_proc_info(thd, "restoring secondary keys"); | |
650 | + | |
651 | + /* Fix the key parts */ | |
652 | + for (i= 0; i < alter_info->delayed_key_count; i++) | |
653 | + { | |
654 | + KEY *key = alter_info->delayed_key_info + i; | |
655 | + KEY_PART_INFO *key_part; | |
656 | + KEY_PART_INFO *part_end; | |
657 | + | |
658 | + part_end= key->key_part + key->key_parts; | |
659 | + for (key_part= key->key_part; key_part < part_end; key_part++) | |
660 | + key_part->field= table->field[key_part->fieldnr]; | |
661 | + } | |
662 | + | |
663 | + if ((error= table->file->add_index(table, alter_info->delayed_key_info, | |
664 | + alter_info->delayed_key_count))) | |
665 | + { | |
666 | + /* | |
667 | + Exchange the key_info for the error message. If we exchange | |
668 | + key number by key name in the message later, we need correct info. | |
669 | + */ | |
670 | + KEY *save_key_info= table->key_info; | |
671 | + table->key_info= alter_info->delayed_key_info; | |
672 | + table->file->print_error(error, MYF(0)); | |
673 | + table->key_info= save_key_info; | |
674 | + | |
675 | + DBUG_RETURN(error); | |
676 | + } | |
677 | + | |
678 | + DBUG_RETURN(0); | |
679 | +} | |
680 | + | |
681 | +/* | |
682 | Alter table | |
683 | ||
684 | SYNOPSIS | |
adf0fb13 | 685 | @@ -6400,19 +6548,38 @@ |
11822e22 AM |
686 | */ |
687 | if (new_table && !(new_table->file->ha_table_flags() & HA_NO_COPY_ON_ALTER)) | |
688 | { | |
689 | + /* | |
690 | + Check if we can temporarily remove secondary indexes from the table | |
691 | + before copying the data and recreate them later to utilize InnoDB fast | |
692 | + index creation. | |
693 | + TODO: is there a better way to check for InnoDB? | |
694 | + */ | |
695 | + bool optimize_keys= (alter_info->delayed_key_count > 0) && | |
696 | + !my_strcasecmp(system_charset_info, | |
697 | + new_table->file->table_type(), "InnoDB"); | |
698 | /* We don't want update TIMESTAMP fields during ALTER TABLE. */ | |
699 | new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; | |
700 | new_table->next_number_field=new_table->found_next_number_field; | |
701 | + | |
702 | thd_proc_info(thd, "copy to tmp table"); | |
703 | DBUG_EXECUTE_IF("abort_copy_table", { | |
704 | my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0)); | |
705 | goto err_new_table_cleanup; | |
706 | }); | |
707 | + | |
708 | + if (optimize_keys) | |
709 | + { | |
710 | + /* ignore the error */ | |
711 | + error= remove_secondary_keys(thd, new_table, alter_info); | |
712 | + } | |
713 | + | |
714 | error= copy_data_between_tables(table, new_table, | |
715 | alter_info->create_list, ignore, | |
716 | order_num, order, &copied, &deleted, | |
717 | alter_info->keys_onoff, | |
718 | alter_info->error_if_not_empty); | |
719 | + if (!error && optimize_keys) | |
720 | + error= restore_secondary_keys(thd, new_table, alter_info); | |
721 | } | |
722 | else | |
723 | { |