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
add comment

1 Answer

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.