Use InnoDB engine for searchindex table instead of MyISAM if supported

[glitchedmob]

Description

I recently came across an issue on one of our client's sites that was having problems searching. Turns out it was related to #3240 and changing the table to use InnoDB fixed the problem. But I'm wondering if maybe we can fix this problem once and for all. Would it be possible to add a check in the initial Craft migration to see if the MySQL version is greater than the version that added full text search to InnoDB tables and if so use the InnoDB engine instead of MyISAM?

I totally understand if this a change that's not worth adding, but I thought it might help people who ran into the same issue I did.

[angrybrad]

This isn't going to be feasible until this MySQL bug is fixed: https://bugs.mysql.com/bug.php?id=78485

(see #3862)

[angrybrad]

Going to go ahead and close this. Considering that bug has been around since 2015 and there are pros and cons for both storage engines using full-text search, think it's better to leave it up to the individual user to decide.

[brandonkelly]

We just implemented a workaround for MySQL bug 78485, so this could be a possibility now. We’ll consider it for Craft 4.

[justinmacleod]

I ran into this issue tonight when attempting to migrate a site to DigitalOcean's Managed Database service. It appears one the limits they impose is that: "You cannot change the storage engine for MySQL from InnoDB to MyISAM.". This means that you can't have tables of mixed storage engines and the import failed. I am running MySQL 8 so I can workaround this due the workaround above - so thanks for that!

Ref: https://www.digitalocean.com/docs/databases/mysql/ under MySQL Limits.