I have a program that needs to insert and update a table very frequently (200+ insert or update queries per minute), and this table should also support fulltext search.

I am currently using MySQL MyISAM, but it will lock the table when some data is inserted. So the fulltext queries go in a queue, slowing them down and eating a lot of memory.

What can I do to get both fulltext indexing and no table locks on inserts or updates?

Database fields:

id | title |  content | url | date | image | source | imagesource

With the current MySQL version this is not possible. Apparently the next version (5.6) will support full text indexing on InnoDB tables. I have no idea when that version will officially be released.

You might also want to consider an upgrade to PostgreSQL which has pretty decent full text indexing (I haven't really used that, but it appears it has some additional features like stemming that MySQL does not have).

And PostgreSQL copes very well with concurrent reads and writes.


You could replicate from InnoDB to MyISAM (see docs). Use the InnoDB-Master for writes and the MyISAM for read and search.

  • InnoDB does not support full text indexing – a_horse_with_no_name Nov 24 '12 at 14:40
  • It is possible (though potentially problematic when transactions are involved) to have InnoDB on the master and MyISAM on the slave for the same table, and a fulltext index on the slave... this doesn't fix the problem -- the MyISAM table on the slave would still be locked for writes caused by updates from the master, which is the problem the question is trying to address. – Michael - sqlbot Nov 24 '12 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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