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:
Whenever
stuff
records are created or updated.Periodically scan "dirty"
stuff
records and updatesearch_index
accordingly.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)
精彩评论