Comments - Adding columns to a table with full text column extremely slow
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.
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]There are many limitations around
FULLTEXT INDEXin InnoDB: https://mariadb.com/resources/blog/initial-impressions-of-innodb-fulltext/MDEV-17459 prevents the
ADD COLUMNfrom being instantaneous. To add insult to the injury, if anALTER TABLEoperation would involve creating multipleFULLTEXT INDEX(like it would if the table were to be rebuilt for adding the columns), then InnoDB would refuse a native operation, and the inefficientALGORITHM=COPYwould 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). TheFORCEkeyword preventsALGORITHM=INSTANToperation 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 INDEXstatements. Note: when you add the first fulltext index, a hiddenFTS_DOC_IDcolumn will be created inside InnoDB. So, typically adding the first fulltext index will require the table to be rebuilt. Also, note that when anyFULLTEXTorSPATIALindex are to exist after theALTER TABLE, the operation cannot be executed online (while allowing concurrent writes to the table).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?