Adding columns to a table with full text column extremely slow
Ok this is the situation -
I am using a script to build about 50 MariaDB tables. None of the tables have any rows in them (they are all empty).
I am adding columns to each table one by one (they are dynamically generated). This runs fast. Adding each column takes a few hundredths of a second.
Now, exact same script, the only difference is I am also adding a full-text index to exactly one column in each table. Now my script runs extremely slow. The "alter table add column" query is taking approximately half a second for every column added afterwards, once the table has a full-text index on any column. The new columns could be just simple int columns with no indexes - it doesn't matter.
Why? There are no records in any table. My script which runs in a few seconds, suddenly balloon up to minutes to run with the only difference being a full-text index added to only one column in each table. Please help.
Answer Answered by Marko Mäkelä in this comment.
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).
