Using ORMLite with MySQL and MyISAM tables

Question

ORMLite's MySQL driver defaults to using InnoDB tables. The documentation describes how to configure it to use other table types, but does not describe the consequences of doing so. Having had bad experiences with InnoDB recently I'd like to migrate to MyISAM, but just wanted to check all features of ORMLite will continue to work correctly if I do, as I know MyISAM lacks some features that are supported by InnoDB. Anyone have any experience of this? Any lurking problems?

(FWIW, I do know my application does not have any particular need of transactions, which is one obvious feature that would fail)

Answer 1

Sorry but I have no idea about the difference between InnoDB and MyISAM from the perspective of the ORM. I would hope that ORMLite is not in any way affected. That the type of table is an internal MySQL designation and may affect performance or query behavior.

Here's a good table of differences between the 2 types that I'll summarize here.

1. InnoDB is newer while MyISAM is older.
2. InnoDB is more complex while MyISAM is simpler.
3. InnoDB is more strict in data integrity while MyISAM is loose.
4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
5. InnoDB has transactions while MyISAM does not.
6. InnoDB has foreign keys and relationship contraints while MyISAM does not. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
7. MyISAM has full-text search index while InnoDB has not.

The only thing that I see on this list which impacts the query-level is that MyISAM does not support foreign-keys or constraints but ORMLite (as of 3/2013) does not support them either. MyISAM does not support transactions but you can make ORMLite calls without them.

Off the top of my head I see nothing on this list that would impact the ORM. That said testing would be a good idea. :-)