I was playing with Percona Server today and found fast_index_creation does not work quite exactly like described in the manual. The point I’m looking to make though it would be very hard to catch this problem without additional
information_schema tables we added in Percona Server.
I was doing simple ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key only copy the data to it and when create secondary key by sort. Looking at innodb_sys_tables and innodb_sys_indexes I discovered it is not the case:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> select * from innodb_sys_tables; +----------+--------+------------------+------+--------+-------+ | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE | +----------+--------+------------------+------+--------+-------+ | 11 | | SYS_FOREIGN | 0 | 7 | 0 | | 12 | | SYS_FOREIGN_COLS | 0 | 7 | 0 | | 14 | sbtest | #sql-430c_2a | 1 | 7 | 11 | | 13 | sbtest | sbtest | 1 | 7 | 10 | +----------+--------+------------------+------+--------+-------+ 4 rows in set (0.00 sec) mysql> select * from innodb_sys_indexes where table_id=13; +----------+---------+----------+------+----------+---------+-------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | +----------+---------+----------+------+----------+---------+-------+ | 15 | PRIMARY | 14 | 3 | 1 | 3 | 11 | | 16 | k | 14 | 0 | 1 | 4 | 11 | +----------+---------+----------+------+----------+---------+-------+ 2 rows in set (0.00 sec) As you can see both primary key and secondary key exist in the table. |
Looking around I found post by Alexey Kopytov which states you also need to set expand_fast_index_creation=1 if you want this feature to work… and indeed it does.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select * from innodb_sys_tables where table_id=18; +----------+--------+--------------+------+--------+-------+ | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE | +----------+--------+--------------+------+--------+-------+ | 18 | sbtest | #sql-430c_2e | 1 | 8 | 15 | +----------+--------+--------------+------+--------+-------+ 1 row in set (0.00 sec) mysql> select * from innodb_sys_indexes where table_id=18; +----------+---------+----------+------+----------+---------+-------+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | +----------+---------+----------+------+----------+---------+-------+ | 24 | PRIMARY | 18 | 3 | 1 | 3 | 15 | +----------+---------+----------+------+----------+---------+-------+ 1 row in set (0.00 sec) |
This table same ALTER TABLE works taking advantage of this feature.
