开发者

How to use FULLTEXT index in MySQL?

Suppose that I have two tables stuff and search_index. For various reasons I would prefer to generate a custom index table with schema along the lines of the following.

I believe that this has to be a separate table as it would require MyISAM storage engine for FULLTEXT support. All of my other tables are InnoDB for transaction support.

search_index (MyISAM):
-----------------------
stuff_id    BIGINT    PRIMARY
keywords    TEXT      FULLTEXT  ; Not same as text in `stuff`
       开发者_开发问答                         ; (optimised & space delimited)

How often should search_index table be updated:

  1. Whenever stuff records are created or updated.

  2. Periodically scan "dirty" stuff records and update search_index accordingly.

  3. Other...

My view is that the benefit of 1 would be easier to maintain and search becomes effective immediately but with an immediate re-index in database. 2 would not be as effective but all reindexing can happen in one hit. Is this true?

How efficient is MySQL at inserting/updating records that have FULLTEXT indexing?

Additional Note: I am trying to keep the schema as portable as possible (different rdbms drivers with PDO)

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜