MySQL Forums
Forum List  »  Full-Text Search

Large DB - Issues
Posted by: abc def
Date: October 06, 2008 06:29AM


We are currently testing MySQL fulltext search for an application that already employs a MySQL backend.

Some specs on the table that we require full-text search (BOOLEAN queries, that is) for:

* 3 fields (id,title,body). Title is a fixed-length string (128chars), body is really plain "fulltext", with an average of 30000 characters.

* we accordingly created a fulltext index for (title,body)

* we require match words with a minimum length of 3 words (ft_min_word_len set accordingly)

* (Development Test) Table Size: 200 MB, Index Size: 30.4 MB (for ~7000 rows) -> we would like the table to grow by at least a factor of 10 though.

The issues are:

1) fulltext search with separate words, i.e. something like '+abc +def' is fast (~0.01s query time), BUT fulltext search with compound expressions, i.e. something like '"abc def"' is SLOW, by orders of magnitude (~ 4-10s query time)!

2) dynamically adding content appears to slow down fulltext search dramatically. Is the index not automatically updated? Only issuing a "REPAIR TABLE bla QUICK;" fixes this. But obviously we can't continuously schedule this command continuously, as it can take a while for completion.

Any ideas or input on how to explain the causes, or on how to go about dealing with said problems?


Options: ReplyQuote

Written By
Large DB - Issues
October 06, 2008 06:29AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.