Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I think it is hard to draw such a conclusion without knowing your table structure, INSERT specifics, other indexes and triggers on that table. Three seconds seem very much for any type of index. –  dezso Oct 17 '12 at 7:33
    
Sorry - don't seen your comment. The table has about 30 varchar and text fields, some indexes on varchar-fields and a fulltext index over the three fields title_text (char(255)), content_text (text), author_text (char(255)). In the table are about 210.000 data sets, the table size is about 2,5 GB and the index-size about 0,5 GB. The insert queries are done for every new data set (no combined query for all inserts). Do you need more informations? –  Timo Oct 18 '12 at 4:35
    
Does your FT index exceed the size of your Key_buffer_Size? I've seen severe performance issues with FT once the indexes are larger then buffers assigned. –  eroomydna Apr 20 at 19:32
add comment

2 Answers

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

share|improve this answer
add comment

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.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.