开发者

How to use FULLTEXT index in MySQL?

开发者 https://www.devze.com 2023-03-25 06:03 出处:网络
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.

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

精彩评论

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