Background ADD INDEX

MySQL 5.6 (and MariaDB 10.0) introduced online ADD INDEX, that is, creating a secondary index while the table is being modified. (It also introduced online table rebuild for cases like adding or dropping columns.)

MariaDB could extend this and support background ADD INDEX, that is, submit a request an index to be created, and do not care when it completes. Technically, the index would exist in the data dictionary, but queries would have to avoid using the index until the storage engine confirms that it is usable.

We cannot support ADD UNIQUE INDEX in the background, because the ability to flag duplicate key errors requires the index to exist. If concurrent INSERT or UPDATE opreations would introduce duplicate keys, that will have to result in the failure of the ADD UNIQUE INDEX operation.

Implementing this feature should require a new storage engine handler call for checking whether an index is usable, and some change of the InnoDB persistent data dictionary. Preferrably, I would first eliminate the InnoDB dictionary tables, such as SYS_INDEXES, and store the secondary index root page numbers and some status information within the .ibd file. This would allow crash-safe operation. (In case the server is killed before the index creation completes, on server restart the index creation would have to start from the beginning, possibly after an explicit statement to request it.)