diff options
| author | Vasil Dimov <vasil.dimov@oracle.com> | 2011-09-07 17:58:10 +0300 |
|---|---|---|
| committer | Vasil Dimov <vasil.dimov@oracle.com> | 2011-09-07 17:58:10 +0300 |
| commit | 10e7b94834e09411367482e07926fd6907e69067 (patch) | |
| tree | cd589dbce761a5c3d7d15d96da42559d261d0e28 | |
| parent | a667fe030b2b03370b2f0eb39e2fa2ed8f13ddf5 (diff) | |
| download | mariadb-git-10e7b94834e09411367482e07926fd6907e69067.tar.gz | |
Use cursors for seeking records in SYS_FOREIGN and SYS_INDEXES from
DROP_TABLE_PROC().
With this change I observe a speedup from 6.2s to 0.1s when executing
DROP_TABLE_PROC() during DROP TABLE with 512 foreign keys, like what
is being done in innodb_bug56143.test
This fixes "Bug#11765460 DROP TABLE USES INEFFICIENT METHODS TO REMOVE
FKS/INDEXES FROM INNODB SYS TABLES"
Reviewed by: Marko
| -rw-r--r-- | storage/innodb_plugin/row/row0mysql.c | 29 |
1 files changed, 19 insertions, 10 deletions
diff --git a/storage/innodb_plugin/row/row0mysql.c b/storage/innodb_plugin/row/row0mysql.c index c4911400cee..4e6a49cf8b0 100644 --- a/storage/innodb_plugin/row/row0mysql.c +++ b/storage/innodb_plugin/row/row0mysql.c @@ -3221,6 +3221,19 @@ check_next_foreign: "index_id CHAR;\n" "foreign_id CHAR;\n" "found INT;\n" + + "DECLARE CURSOR cur_fk IS\n" + "SELECT ID FROM SYS_FOREIGN\n" + "WHERE FOR_NAME = :table_name\n" + "AND TO_BINARY(FOR_NAME)\n" + " = TO_BINARY(:table_name)\n" + "LOCK IN SHARE MODE;\n" + + "DECLARE CURSOR cur_idx IS\n" + "SELECT ID FROM SYS_INDEXES\n" + "WHERE TABLE_ID = table_id\n" + "LOCK IN SHARE MODE;\n" + "BEGIN\n" "SELECT ID INTO table_id\n" "FROM SYS_TABLES\n" @@ -3243,13 +3256,9 @@ check_next_foreign: "IF (:table_name = 'SYS_FOREIGN_COLS') THEN\n" " found := 0;\n" "END IF;\n" + "OPEN cur_fk;\n" "WHILE found = 1 LOOP\n" - " SELECT ID INTO foreign_id\n" - " FROM SYS_FOREIGN\n" - " WHERE FOR_NAME = :table_name\n" - " AND TO_BINARY(FOR_NAME)\n" - " = TO_BINARY(:table_name)\n" - " LOCK IN SHARE MODE;\n" + " FETCH cur_fk INTO foreign_id;\n" " IF (SQL % NOTFOUND) THEN\n" " found := 0;\n" " ELSE\n" @@ -3259,12 +3268,11 @@ check_next_foreign: " WHERE ID = foreign_id;\n" " END IF;\n" "END LOOP;\n" + "CLOSE cur_fk;\n" "found := 1;\n" + "OPEN cur_idx;\n" "WHILE found = 1 LOOP\n" - " SELECT ID INTO index_id\n" - " FROM SYS_INDEXES\n" - " WHERE TABLE_ID = table_id\n" - " LOCK IN SHARE MODE;\n" + " FETCH cur_idx INTO index_id;\n" " IF (SQL % NOTFOUND) THEN\n" " found := 0;\n" " ELSE\n" @@ -3275,6 +3283,7 @@ check_next_foreign: " AND TABLE_ID = table_id;\n" " END IF;\n" "END LOOP;\n" + "CLOSE cur_idx;\n" "DELETE FROM SYS_COLUMNS\n" "WHERE TABLE_ID = table_id;\n" "DELETE FROM SYS_TABLES\n" |