Docs need to clarify the difference between Fast Index Creation and Expanded Fast Index Creation better
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Hrvoje Matijakovic | ||
5.1 |
Fix Released
|
Medium
|
Hrvoje Matijakovic | ||
5.5 |
Fix Released
|
Medium
|
Hrvoje Matijakovic |
Bug Description
From the manual:
http://
When ALTER TABLE requires a table copy, secondary keys are now dropped and recreated later, after copying the data. The following restrictions apply:
Only non-unique keys can be involved in this optimization.
If the table contains foreign keys, or a foreign key is being added as a part of the current ALTER TABLE statement, the optimization is disabled for all keys.
I'm running the following ALTER TABLE:
mysql> alter table sbtest add z int not null;
Looking at sys_indexes/
mysql> select * from innodb_sys_tables where table_id=16;
+------
| TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+------
| 16 | sbtest | #sql-430c_2a | 1 | 8 | 13 |
+------
1 row in set (0.00 sec)
mysql> select * from innodb_sys_indexes where table_id=16;
+------
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+------
| 20 | PRIMARY | 16 | 3 | 1 | 3 | 13 |
| 21 | k | 16 | 0 | 1 | 4 | 13 |
+------
2 rows in set (0.00 sec)
So both primary index and secondary index "k" seems to be created for temporary tables, while I would expect only
primary index created and secondary index added later.
Related branches
- Alexey Kopytov (community): Approve
-
Diff: 45 lines (+1/-27)1 file modifieddoc/source/management/innodb_fast_index_creation.rst (+1/-27)
- Alexey Kopytov (community): Approve
-
Diff: 3494 lines (+3324/-23)11 files modified.bzrignore (+0/-1)
doc/Makefile (+5/-0)
doc/source/management/innodb_fast_index_creation.rst (+1/-22)
doc/source/percona-theme/layout.html (+407/-0)
doc/source/percona-theme/searchbox.html (+22/-0)
doc/source/percona-theme/static/default.css_t (+433/-0)
doc/source/percona-theme/static/jquery.min.js (+154/-0)
doc/source/percona-theme/static/percona.com.css (+1850/-0)
doc/source/percona-theme/static/percona.com.js (+269/-0)
doc/source/percona-theme/static/sidebar.js (+151/-0)
doc/source/percona-theme/theme.conf (+32/-0)
Looking more into the problem it looks like the problem is in documentation which does NOT mention expand_ index_creation variable:
mysql> show variables like "expand_ fast_index_ creation" ; ------- ------- ------- -+----- --+ ------- ------- ------- -+----- --+ fast_index_ creation | OFF | ------- ------- ------- -+----- --+
+------
| Variable_name | Value |
+------
| expand_
+------
1 row in set (0.00 sec)
By the way any reason to still keep it OFF by default ?