Tell me more ×
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.
up vote 2 down vote favorite

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
share|improve this question

2 Answers

up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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

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

 
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.