So as of Composr we do support InnoDB if someone wants to manually switch all their tables to it. For that we did have to make improvements, as there are subtle performance ramifications with InnoDB -- particularly that COUNT(*) queries are massively slower on InnoDB. InnoDB will be slower for individual queries, but faster under load due to smarter locking. InnoDB is also harder to recover if corrupted, while MyISAM crashes far too much. So it's not a trivial comparison.

I'd do the switch today though, if we could rely on MySQL 5.6 being on web hosts. As it stands we're already pushing things with our requirement for MySQL 5.5.3.

Yeah InnoDB doesn't offer much benefit over MyISAM, I'll agree. I wish there was a really good widely-supported database engine out there. But they all have their own pros and cons.

And when innodb corrupts, you have a VERY valid point there. I have never been able to recover a corrupted InnoDB database before.

Actually row-level locking is a huge advantage of InnoDB, so I would recommend it for any large site.

For now we will leave it MyISAM. In a few years when we can be sure everyone has at least MySQL 5.6, we will re-visit and likely fully drop MyISAM in favour of InnoDB.

I don't want us to officially support both as supporting just one right now allows us to give better advice. For example, one of the tutorials references the [tt]myisamchk[/tt] tool to repair tables.

Also I've recently seen actually bugginess on InnoDB, https://bugs.mysql.com/bug.php?id=81031 happened to a customer recently.

We actually will need to impose a restriction of MySQL 5.7+, or innodb_large_prefix being set to ON. Otherwise we are not able to use the same keys we currently use due to a limit of 767 bytes on the index (which is just one VARCHAR(255) on utf8mb3).

Another point of note is that InnoDB doesn't have the 64 index limit MyISAM has. So we could remove the check code we currently have (well, better, code the database drivers to know what the limit is).

I'm highly considering implementing official InnoDB support in v11 as-in making it a DB driver option on the install screen (but keeping MyISAM as an option as well for the time being.

- MySQL is not supported below 8.0 anymore so we can be almost certain everyone has at least 5.7
- InnoDB has more benefits than drawbacks compared to MyISAM (especially now that v11 is much more aggressive with stats)
- Another benefit not mentioned is InnoDB supports foreign keys and constraints, so it can act as a secondary safeguard for data integrity, or at least a way to auto-prune orphaned records

Shouldn't be too difficult; it's already partially implemented.
- Migrate meta-toolkit code into core
- Create new table... db_meta_relations, to replace get_relation_map function
- Add new DB driver functions create_foreign_key / edit_foreign_key / delete_foreign_key. These will always modify db_meta_relations, but they will only apply the foreign key if the driver supports it (in our case, if using MySQL and InnoDB).
- Add the ability to migrate an existing MyISAM database to InnoDB by creating new tables and migrating the data (if at all possible. If not, have clear documentation on how to do it).
- In the DB integrity scan, ensure foreign keys are set correctly according to db_meta_relations. Add a test for this as well.
- Use db_meta_relations instead of get_relation_map / get_relation_map_by_table
- Add InnoDB as an option on the installer... selected by default now (except when running in dev mode, in that case it will randomise between MyISAM and InnoDB)
- Deprecate MyISAM support, plan for removal in v12