Comments - Adding columns to a table with full text column extremely slow

3 years, 5 months ago Marvin Herbold

Hello -

This is using InnoDB.

Here is part of the output from my script,without any full-text columns - the numbers in brackets like [0.007926] is the timing in seconds.

            >>> Create / modify columns
                >>> Module display name
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `displayName` varchar(191) NOT NULL DEFAULT ?
                    <<< Adding the column [0.007926]
                    >>> Creating b-tree index
CREATE INDEX `idx_displayName_btree` ON `wdeModule` (`displayName`)
                    <<< Creating b-tree index [0.034811]
                <<< Module display name [0.043111]
                >>> List of records page
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `listOfRecordsPageId` int(10) unsigned NOT NULL DEFAULT ? AFTER `displayName`
                    <<< Adding the column [0.009343]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_listOfRecordsPageId` FOREIGN KEY (`listOfRecordsPageId`) REFERENCES `wdePage` (`id`)
                    <<< Creating foreign key [0.03307]
                <<< List of records page [0.042825]
                >>> Record details page
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `recordDetailsPageId` int(10) unsigned NOT NULL DEFAULT ? AFTER `listOfRecordsPageId`
                    <<< Adding the column [0.010019]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_recordDetailsPageId` FOREIGN KEY (`recordDetailsPageId`) REFERENCES `wdePage` (`id`)
                    <<< Creating foreign key [0.02976]
                <<< Record details page [0.040282]
                >>> Parameter
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `parameterId` int(10) unsigned NOT NULL DEFAULT ? AFTER `recordDetailsPageId`
                    <<< Adding the column [0.008562]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_parameterId` FOREIGN KEY (`parameterId`) REFERENCES `wdeParameter` (`id`)
                    <<< Creating foreign key [0.025838]
                <<< Parameter [0.034919]
                >>> Context
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `contextId` int(10) unsigned NOT NULL DEFAULT ? AFTER `parameterId`
                    <<< Adding the column [0.010369]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_contextId` FOREIGN KEY (`contextId`) REFERENCES `wdeContext` (`id`)
                    <<< Creating foreign key [0.031535]
                <<< Context [0.042347]

Here is the same part of the same script, only with the full-text index added to the displayName column.  Look at each "<<< Adding the column" line after that and note how every new column after the full-text one now takes about 0.3 to 0.4 second while previously it was much faster (hundredths or thousandths of a second).

            >>> Create / modify columns
                >>> Module display name
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `displayName` varchar(191) NOT NULL DEFAULT ?
                    <<< Adding the column [0.010926]
                    >>> Creating b-tree index
CREATE INDEX `idx_displayName_btree` ON `wdeModule` (`displayName`)
                    <<< Creating b-tree index [0.032191]
                    >>> Creating full-text index
CREATE FULLTEXT INDEX `idx_displayName_fulltext` ON `wdeModule` (`displayName`)
                    <<< Creating full-text index [0.261039]
                <<< Module display name [0.304855]
                >>> List of records page
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `listOfRecordsPageId` int(10) unsigned NOT NULL DEFAULT ? AFTER `displayName`
                    <<< Adding the column [0.396051]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_listOfRecordsPageId` FOREIGN KEY (`listOfRecordsPageId`) REFERENCES `wdePage` (`id`)
                    <<< Creating foreign key [0.032997]
                <<< List of records page [0.42954]
                >>> Record details page
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `recordDetailsPageId` int(10) unsigned NOT NULL DEFAULT ? AFTER `listOfRecordsPageId`
                    <<< Adding the column [0.394164]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_recordDetailsPageId` FOREIGN KEY (`recordDetailsPageId`) REFERENCES `wdePage` (`id`)
                    <<< Creating foreign key [0.031623]
                <<< Record details page [0.426277]
                >>> Parameter
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `parameterId` int(10) unsigned NOT NULL DEFAULT ? AFTER `recordDetailsPageId`
                    <<< Adding the column [0.408285]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_parameterId` FOREIGN KEY (`parameterId`) REFERENCES `wdeParameter` (`id`)
                    <<< Creating foreign key [0.037448]
                <<< Parameter [0.446114]
                >>> Context
                    >>> Adding the column
ALTER TABLE `wdeModule` ADD COLUMN `contextId` int(10) unsigned NOT NULL DEFAULT ? AFTER `parameterId`
                    <<< Adding the column [0.417676]
                    >>> Creating foreign key
ALTER TABLE `wdeModule` ADD CONSTRAINT `fk_wdeModule_contextId` FOREIGN KEY (`contextId`) REFERENCES `wdeContext` (`id`)
                    <<< Creating foreign key [0.033758]
                <<< Context [0.451862]
 
3 years, 5 months ago Marko Mäkelä

There are many limitations around FULLTEXT INDEX in InnoDB: https://mariadb.com/resources/blog/initial-impressions-of-innodb-fulltext/

MDEV-17459 prevents the ADD COLUMN from being instantaneous. To add insult to the injury, if an ALTER TABLE operation would involve creating multiple FULLTEXT INDEX (like it would if the table were to be rebuilt for adding the columns), then InnoDB would refuse a native operation, and the inefficient ALGORITHM=COPY would be used.

You could try dropping all fulltext indexes first, and then perform ALTER TABLE t FORCE, ADD COLUMN …, ADD FULLTEXT INDEX …, ALGORITHM=INPLACE (creating at most one fulltext index). The FORCE keyword prevents ALGORITHM=INSTANT operation and forces a rebuild. It should not be necessary, but I did not test it.

If there are multiple fulltext indexes, you can create them one at a time with subsequent ALTER TABLE t ADD FULLTEXT INDEX statements. Note: when you add the first fulltext index, a hidden FTS_DOC_ID column will be created inside InnoDB. So, typically adding the first fulltext index will require the table to be rebuilt. Also, note that when any FULLTEXT or SPATIAL index are to exist after the ALTER TABLE, the operation cannot be executed online (while allowing concurrent writes to the table).

 
3 years, 5 months ago Marvin Herbold

Ok I understand what you are saying, i think... but these tables are completely empty (has zero rows). So, even if the table has to be completely re-built... it's an empty table. Why is it taking so long?

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.