A couple of installs are seeing various concrete problems with the MyISAM search index tables, and we've seen sporadic issues in the past ("table needs repair", etc). Broadly, MyISAM is known to be a less-than-perfect database engine. However, it was our only option for FULLTEXT indexes for some time.
On relatively recent versions of MySQL, FULLTEXT indexes are supported on the InnoDB engine. Ideally, we should detect that MySQL has InnoDB FULLTEXT support and then have storage adjust use the InnoDB engine instead of the MyISAM engine for the FULLTEXT tables.
However, it's at least slightly more complicated than that:
- We need to detect that this capability is present (what command can we run to ask MySQL if it supports InnoDB FULLTEXT?)
- InnoDB uses a different stopword mechanism: a stopword table instead of a stopword file. We need to generate this table (just unconditionally?) and configure it or have users configure it.
- Does InnoDB FULLTEXT respect ft_boolean_syntax? If no: is there another similar option? If no: we need to parse queries ourselves and submit the +A +B version to the backend (see also T10642).
- Does InnoDB FULLTEXT respect ft_min_word_len? If no: is there another similar option? If no: maybe we need to filter short words out when indexing/querying?
- Does swapping the ENGINE on an existing MyISAM table also rebuild the index? Do we need to get users to run search index --all after this change? If so, how do we tell them?