When does MySQL rebuild a FTS index?

Question

If you want to use the MySQL Full Text Search functionality you define a Full Text Index on field(s) with type VARCHAR. As rows are INSERTed and UPDATEd, mysql has to keep the indexes up-to-date. My question is: When does MySQL rebuild a FTS index?

• A) Immediately after a INSERT or UPDATE occurs that affects the index.
• B) When the first SELECT is run that needs the index which has recently been affected by a UPDATE or INSERT.
• C) Something else.

---

Unecessary background info: My experience seems to think option B. Is this right? I ask because I have been experiencing sudden random slow queries which do a Full Text Search and I don't know why some are slow and not the others. My hunch is that the queries can be slow if they are waiting for mysql to rebuild the FTS index, but I don't know if this is how mysql works. Example of a random slow query (usually the same query runs under a second), there is no slow UPDATE or INSERT in the slow log:

# Query_time: 61.775612  Lock_time: 10.110924 Rows_sent: 20  Rows_examined: 222498
SELECT SQL_CALC_FOUND_ROWS id
FROM members
WHERE
    AND MATCH (bio,profile_text,name) AGAINST ('building surveyor')
    AND MATCH (town, postcode, country) AGAINST ('united kingdom, liverpool');

Notice the Lock_time. I can't see any other INSERT or UPDATE in the slow log, so I'm not sure what it is waiting for. That is why I guessed it could be waiting for a FTS index rebuild?

Answer 1

Consider using ... AGAINST('"building surveyor"') to get that phrase instead of those two words scattered anywhere.

Are you using MyISAM or InnoDB? They work differently.

MyISAM, I think, updates the FT indexes when a row is inserted/updated.

InnoDB, I think, queues up changes, and eventually writes them to disk. This generally makes it faster for writes than MyISAM.

Neither Engine rebuilds the index except when explicitly asked to.