Docs need to clarify the difference between Fast Index Creation and Expanded Fast Index Creation better

[Peter Zaitsev]

From the manual:
http://www.percona.com/doc/percona-server/5.5/management/innodb_fast_index_creation.html

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/sys_tables I can see:

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.

[Peter Zaitsev (pz-percona)]

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";
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| expand_fast_index_creation | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)

By the way any reason to still keep it OFF by default ?

[Laurynas Biveinis (laurynas-biveinis)]

Peter -

So does it work as you expect once you turn on the expand_fast_index_creation?

I think we have a quite big confusion due to two similar but not really related features: 1) fast_index_creation that is enabled by default, [1], and I don't even get what's documented there - is this an upstream feature that we have option to disable? and 2) expanded_fast_index_creation that is disabled by default and seems actually to be required for ALTER TABLE etc. optimizations to actually work.

I think these two doc items should be rewritten to increase clarity and distinction between the features.

[1] www.percona.com/doc/percona-server/5.5/management/innodb_fast_index_creation.html
[2] www.percona.com/doc/percona-server/5.5/management/innodb_extended_fast_index_creation.html

[Alexey Kopytov (akopytov)]

Yes, I think we still need to keep expand_fast_index_creation OFF by default. See bug #858945.

[Laurynas Biveinis (laurynas-biveinis)]

I am re-opening this as a doc bug. The two doc links in my previous comment, while technically correct, are not very clear on what needs to be enabled when and on fast_index_creation vs. expanded_fast_index_creation distinction.

Feel free to close if the consensus is that the current docs are perfectly fine.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1259