MyISAM or InnoDB?

eriklharper

Any particular reason why the Fuel tables use the MyISAM table type instead of the InnoDB type? I'm not a huge expert in MySQL and intricate differences between the two types other than what I've read in articles and in the MySQL manual, but it does seem like there are some benefits to using InnoDB, so I thought I would ask you guys if it matters or not.

Erik

admin

There really isn't a good reason other then when we first started with FUEL that was the default storage engine for MySQL.

sid4it

We could potentially modify the schema and migrate to innodb and still use the framework. Nothing is stopping from doing it after it is being deployed right?

Do you see/anticipate issues?

eriklharper

Sounds good. I'm wondering if the schema should be modified to basically take out the engine designation for all the fuel tables, that way the table engine will be selected based on the default engine setup for the MySQL version that the fuel app will eventually use.

Michaelo

Fulltext indexing is an advantage in certain circumstances but is only supported with MyISAM... so if you leave the engine out be sure you never use Fulltext...

Lance

I wouldn't leave it to some default setting. There's no point using innodb if you have no relationships to enforce. Table's should use what they need to.

There's plenty of spots where Fuel creates logical relationships but they're not *actual* relationships. One benefit (to some, perhaps not for all) would be cascading deletes.. could negate most of the on_after_deletes.

EcotypeS

I'll give you another reason to stay with MyISAM. Totally unrelated to FUEL, but potentially deadly.

I have a site running on Bluehost. Uses MySQL (my FUEL DB). Based on some of the same recent info on InnoDB mentioned here, I decided to try it. No practical difference in response time. BUT, huge BUT, the table got corrupted (server problem). phpMyAdmin mostly would not function and would not export or backup anything. Apparently the MySQL console that Bluehost uses would not work either. Bluehost claimed the problem as theirs, but their only proposed solution was to reload then entire DB from a seriously old backup (my fault for the backup age). They specifically said that MySQL's recovery mechanisms do not work with InnoDB tables.

Luckily, I happened to know that my php code was accessing other tables in the DB successfully and wrote a manual backup of all tables except the one that was corrupt.

But based on this experience, avoid InnoDB tables. Needless to say, I'm not planning to use those again any time soon.

Lance

Personally, that's not a good reason to avoid innodb. You had a corrupted table, it happens. Could have happened with any storage engine. There are tools to recover (EG: http://code.google.com/p/innodb-tools/) but a better bet is to have cron backups running.

That's some pretty bad luck though to swap to them (for the first time?) and have a corruption. I've not had one for about 8 years!