Slow insert with MySQL full-text index

Question

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right?

How can I get better performance from the INSERT? Is there perhaps an option to set when MySQL rebuilds the full-text index?

Answer 1

Since you are dealing with MyISAM, I have a small suggestion.

If you can collect the rows in a separate table that does not have a FULLTEXT index. You can bulk load them into the table that has the fulltext index.

For example: For a MyISAM table mydb.datafulltxt, try the following

Step 01) CREATE TABLE mydb.dataonly LIKE mydb.datafulltxt;

Step 02) Drop the FULLTEXT index out of mydb.dataonly

Step 03) Do many INSERTs into mydb.dataonly

Step 04) Run this sequence of commands every hour to load them into mydb.datafulltxt

ALTER TABLE mydb.datafulltxt DISABLE KEYS;
INSERT INTO mydb.datafulltxt SELECT * FROM mydb.dataonly;
ALTER TABLE mydb.datafulltxt ENABLE KEYS;
CREATE TABLE mydb.dataonlynew LIKE mydb.dataonly;
ALTER TABLE mydb.dataonly RENAME mydb.dataonlyold;
ALTER TABLE mydb.dataonlynew RENAME mydb.dataonly;
DROP TABLE mydb.dataonlyold;

Step 05) Repeat from Step 03

Hopefully, bulk loading may help if your app is designed to do it and you can tolerate it

Answer 2

It must be as quick if not quicker to do:

1. ALTER TABLE mydb.datafulltxt DISABLE KEYS;
2. Do many INSERTs into mydb.datafulltxt
3. ALTER TABLE mydb.datafulltxt ENABLE KEYS;
4. Repeat as required.