content_image/770322.jpg content_image/770318.jpg
mysql> CREATE TABLE opening_lines ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200) ) ENGINE=InnoDB; 

When you create a full-text index on the table using CREATE FULLTEXT INDEX  syntax, a warning is returned which reports that InnoDB  is rebuilding the table to add the FTS_DOC_ID  column. 

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line); Query OK, 0 rows affected, 1 warning (0.19 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 

The same warning is returned when using ALTER TABLE  to add a full-text index to a table that does not have an FTS_DOC_ID  column. If you create a full-text index at CREATE TABLE  time and do not specify an FTS_DOC_ID  column, InnoDB  adds a hidden FTS_DOC_ID  column, without warning. 

Defining an FTS_DOC_ID  column at CREATE TABLE  time is less expensive than creating a full-text index on a table that is already loaded with data. If an FTS_DOC_ID  column is defined on a table prior to loading data, the table and its indexes do not have to be rebuilt to add the new column. If you are not concerned with CREATE FULLTEXT INDEX  performance, leave out the FTS_DOC_ID  column to have InnoDB  create it for you. InnoDB  creates a hidden FTS_DOC_ID  column along with a unique index ( FTS_DOC_ID_INDEX ) on the FTS_DOC_ID  column. If you want to create your own FTS_DOC_ID  column, the column must be defined as BIGINT UNSIGNED NOT NULL  and named FTS_DOC_ID  (all uppercase), as in the following example: 

<img src='content_image/770322.jpg'>

mysql> CREATE TABLE opening_lines ( FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200) ) ENGINE=InnoDB; 

## Note 

The FTS_DOC_ID  column does not need to be defined as an AUTO_INCREMENT column, but doing so could make loading data easier. 

If you choose to define the FTS_DOC_ID  column yourself, you are responsible for managing the column to avoid empty or duplicate values. FTS_DOC_ID  values cannot be reused, which means FTS_DOC_ID values must be ever increasing. 

Optionally, you can create the required unique FTS_DOC_ID_INDEX  (all uppercase) on the FTS_DOC_ID column. 

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID); 

If you do not create the FTS_DOC_ID_INDEX , InnoDB  creates it automatically. 

<img src='content_image/770318.jpg'>

## Note 

FTS_DOC_ID_INDEX  cannot be defined as a descending index because the InnoDB  SQL parser does not use descending indexes. 

