[ENH] mysql search index: switch to InnoDB engine

• [ENH] mysql search index: switch to InnoDB engine, partition the big index table into smaller sized table indexes to avoid hard-limitation of InnoDB storage - row size limit
• the biggest possible number of fields per table is calculated dynamically based on innodb storage requirements

Notes from Marc: Now that we don't support ancient MySQL/MariaDB versions, we can switch Tiki's Unified index from MyISAM FULLTEXT to InnoDB FULLTEXT. My summary evaluation is that InnoDB FULLTEXT is nowadays generally a much better solution. There could be drawbacks but we will only know once we start using. And InnoDB FULLTEXT is expected to continue to improve. Here are various links comparing these 2 options:

• https://stackoverflow.com/questions/45669229/does-mysqls-fulltext-search-return-the-same-results-for-myisam-and-innodb
• https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html
• https://www.percona.com/blog/myisam-vs-innodb-full-text-search-in-mysql-5-6-part-1/
• https://www.percona.com/blog/innodb-mysql-full-text-search-in-mysql-5-6-part-2-the-queries/
• https://www.percona.com/blog/innodb-full-text-search-in-mysql-5-6-part-3/
• https://www.slideshare.net/mattalord/getting-started-with-mysql-full-text-search
• https://dev.mysql.com/blog-archive/rankings-with-innodb-full-text-search/
  • "In short, the InnoDB document search and relevancy rankings were modeled after the open source Sphinx full-text search engine, and not after the MyISAM storage engine (which sometimes simply produces incorrect results). The algorithms used for the InnoDB implementation are based on the well known BM25 and TF-IDF ranking algorithms."
    • Manticore Search is the evolution of Sphinx Search
• https://mysqlentomologist.blogspot.com/2018/03/on-innodbs-fulltext-indexes.html
  • "To summarize, InnoDB FULLTEXT indexes is one of the most problematic InnoDB features for any production use"

https://avan.tech/item101108