› ›
CRITICAL SECURITY VERSION UPDATE:
https://github.com/daylightstudio/FUEL-CMS/releases/tag/1.4.13

MyISAM or InnoDB?

eriklharpereriklharper
edited November 2011 in Installation
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

Comments

  • adminadmin
    edited 11:27AM
    There really isn't a good reason other then when we first started with FUEL that was the default storage engine for MySQL.
  • sid4itsid4it
    edited 11:27AM
    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?
  • eriklharpereriklharper
    edited 11:27AM
    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.
  • MichaeloMichaelo
    edited 11:27AM
    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...
  • LanceLance
    edited 11:27AM
    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.
  • EcotypeSEcotypeS
    edited 11:27AM
    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.
  • LanceLance
    edited November 2011
    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!
Sign In or Register to comment.