The search feature that hurt me most in production was not Elasticsearch. It was the InnoDB FULLTEXT index on a marketplace table with about 4.2 million listings, added in an afternoon because MATCH ... AGAINST was already there and the deadline was not. It worked for months. Then traffic tripled, a seller uploaded a few hundred thousand listings whose titles were mostly Japanese, and search latency went from 90 milliseconds to nearly two seconds while support tickets claimed search was ignoring half the words. It was. Literally. That week taught me where the edges of InnoDB full-text search actually are, and they sit much closer than the syntax suggests.
The syntax hides the machinery. A FULLTEXT index in InnoDB is not one index but a set of hidden auxiliary tables, with its own parser, its own stopword list, its own deferred-delete maintenance, and a ranking function that stopped evolving right around the time BM25 became table stakes everywhere else. Here is what lives under the hood, which knobs force a rebuild, what the FTS_DOC_ID column will do to your ALTERs, and the honest point where you should stop asking InnoDB to be a search engine.
How does InnoDB actually store a FULLTEXT index?
As a group of hidden auxiliary InnoDB tables forming an inverted index, plus bookkeeping tables for config and deferred deletes. When you create a FULLTEXT index, InnoDB builds several tables named like FTS_0000000000000abc_0000000000000def_INDEX_1 through INDEX_6 — the token partitions — alongside FTS_..._CONFIG, FTS_..._DELETED, FTS_..._DELETED_CACHE, and FTS_..._BEING_DELETED tables that track the index's state. Fresh tokens first land in an in-memory cache sized by innodb_ft_cache_size and get merged into the inverted index in batches — queries consult the cache and the on-disk index together, so a freshly inserted row is searchable immediately; what the batching defers is the disk fan-out, not the visibility. You can see the whole family through information_schema, and pointing innodb_ft_aux_table at your table unlocks the full-text inspector views:
-- the hidden tables behind one FULLTEXT index
SELECT NAME, N_COLS
FROM information_schema.INNODB_TABLES
WHERE NAME LIKE 'shop/FTS_%';
-- inspect the inverted index itself
SET GLOBAL innodb_ft_aux_table = 'shop/listings';
SELECT WORD, FIRST_DOC_ID, LAST_DOC_ID, DOC_COUNT
FROM information_schema.INNODB_FT_INDEX_TABLE
LIMIT 10;
SELECT WORD, DOC_COUNT
FROM information_schema.INNODB_FT_INDEX_CACHE
LIMIT 10;The practical consequences are real. Backups copy all of these tables like any other InnoDB data, the index is crash-safe for the same reason your rows are, but one index is really a dozen tables, and every write to a full-text column eventually fans out into token-level writes across them. On write-heavy tables that fan-out is a quiet tax on the buffer pool that nobody attributes to search until they profile it, and it is one more reason to size the pool with search writes in mind — the reasoning in buffer pool sizing applies here too.
Why do short words and common words silently vanish from search?
Because tokens shorter than innodb_ft_min_token_size are never indexed, and the built-in stopword list is applied at index time — both decisions are baked into the index the moment it is built. The minimum defaults to 3, so go, up, TV, and every two-letter product code on your site return zero rows while the data sits right there; the maximum, innodb_ft_max_token_size, defaults to 84 and rarely matters. Stopwords are the other half: a built-in list drops words like the, and, and about entirely. You can replace it globally with innodb_ft_server_stopword_table pointing at your own table or per session with innodb_ft_user_stopword_table — an empty value falls back to the built-in list rather than disabling it — and the only real off switch is innodb_ft_enable_stopword=OFF at index-build time, which means a rebuild. The trap that gets everyone, including past me: changing innodb_ft_min_token_size does nothing until you rebuild every affected FULLTEXT index, either by dropping and re-adding it or by rebuilding the table. On that marketplace, eleven percent of single-word queries contained a two-letter token. Every one of those users saw an empty results page and assumed the item was gone.
What is the FTS_DOC_ID trap?
Every InnoDB full-text table needs a BIGINT UNSIGNED column called FTS_DOC_ID mapping documents to tokens, and if it does not exist, InnoDB rebuilds the entire table to add it the first time you create a FULLTEXT index. That means a full row-by-row copy of your 300GB table on the day someone runs an innocent-looking CREATE FULLTEXT INDEX, plus a hidden unique index named FTS_DOC_ID_INDEX on top. If you define the column yourself to control when that cost lands, it must be exactly BIGINT UNSIGNED NOT NULL, and getting the definition wrong produces cryptic errors about the column type instead of a useful message. Two more behaviors surprise people: the column never appears in SELECT * output or SHOW CREATE TABLE in an obvious way, which confuses schema-diff tools and ORM introspection, and dropping your last FULLTEXT index does not remove it — InnoDB keeps the column and the auxiliary tables on purpose, precisely so that re-adding a FULLTEXT index later does not force another base-table rebuild. My position: define FTS_DOC_ID yourself, at table creation time, on any table you expect to search. Do it while the table is small and the rebuild is a non-event, because adding search to a big table is exactly the kind of change covered in what actually qualifies for instant DDL — and this one does not qualify.
How do you make CJK text searchable with the ngram parser?
By building the index with WITH PARSER ngram, which tokenizes text into overlapping fixed-size character runs instead of splitting on whitespace. The default parser assumes words are separated by spaces and punctuation, which is fine for English and useless for Chinese, Japanese, and Korean: a string of characters with no spaces becomes one giant token that only matches itself. The ngram parser, built in since 5.7, emits overlapping bigrams by default — ngram_token_size is 2, set it at startup, and like the token-size knobs it only takes effect on indexes built afterward. Japanese also has the optional MeCab parser plugin for real word segmentation. Rebuilding the marketplace index with ngram is what finally made Japanese titles findable, and the cost was a noticeably larger index, because every character participates in multiple tokens. One caution that bit us later: charset and collation choices interact with parsing, so get those right first — the field notes in utf8mb4 collation choices are worth reading before you build the index, not after.
Why does the index grow after deletes, and how do you maintain it?
Because full-text deletes are deferred: deleting a row only records its document ID in the DELETED auxiliary table, and the row's tokens stay in the inverted index until you merge them out with OPTIMIZE TABLE. Queries filter out the dead document IDs as they go, so results stay correct while the index grows monotonically and query latency creeps up with the pile of dead IDs being filtered. On churn-heavy tables — listings that expire, messages that get deleted — this is a slow leak that nobody monitors. The maintenance routine I settled on:
-- merge the full-text index WITHOUT rebuilding the whole table
SET GLOBAL innodb_optimize_fulltext_only = ON;
-- merge at most this many words per OPTIMIZE run; repeat until done
SET GLOBAL innodb_ft_num_word_optimize = 2000;
OPTIMIZE TABLE listings;
-- watch the deferred-delete backlog shrink
SELECT * FROM information_schema.INNODB_FT_DELETED LIMIT 5;
SELECT * FROM information_schema.INNODB_FT_BEING_DELETED LIMIT 5;Without innodb_optimize_fulltext_only, OPTIMIZE TABLE rebuilds the entire table — rows and all indexes — which on a big table is an expensive way to clean a search index. With it, the statement only merges the full-text structures, innodb_ft_num_word_optimize bounds each run, and you loop it off-peak until the DELETED views drain. I schedule it weekly on churn-heavy search tables and have never had to think about full-text bloat since.
When should you move search out of MySQL?
The moment you need relevance tuning, typo tolerance, synonyms, faceting, or highlighting — InnoDB's ranking is a fixed BM25/TF-IDF blend with almost no controls. Natural-language mode ranks on term frequency, inverse document frequency, and document length normalization, and that is the whole story: no field boosting, so you cannot make title matches outrank body matches in a single query without concatenating two MATCH scores yourself; no analyzers or synonym expansion; no fuzzy matching for misspellings; no aggregations for faceted navigation; no snippet highlighting. Boolean mode gives you operators that application developers routinely misuse, and multi-column MATCH must span exactly the columns of one index. My line after a decade of this: if search is a feature your customers consciously use — autocomplete, ranked browsing, forgiving typos — run a dedicated search engine beside MySQL, keep MySQL as the system of record, and feed changes across with binlog-based CDC. If search is an admin filter over a hundred thousand rows, FULLTEXT is genuinely fine and one less distributed system to operate. The failure mode is the middle: a product that grows from the second case into the first while nobody revisits the decision.
Where MonPG stands on MySQL
I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals in this piece — full-text auxiliary table growth, the deferred-delete backlog in the DELETED views, search latency drifting after tokenization changes — are exactly the kind of thing the MySQL work is designed to surface as timelines instead of support tickets. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.