I am switching servers and will be updating to the latest mySQL. My current database is myISAM and I was considering converting to innoDB. I have heard that innoDB is “better” but was wondering what the benefits are in regards to servoy - the pitfalls?
Can anyone with mySQL experience chime in with a good arguement for either? We are also going to be accessing the data via PHP5.
We currently use servoy for our back-office and PHP to SELECT data only, for customers and internal reports.
gzola:
Servoy needs of a DB that supports exactly the transactions.
Only the Servoy repository needs a database with transaction support (to guarantee integrity of what you are developing), all other database connections can be of any type.
Other than that the biggest difference is:
innodb: more security (in terms of possible data loss and corruption)
myisam: faster
I’d only use myisam if loads of querying has to be done and it’s no big deal if data is lost (eg lookup tables with millions of records)
Go for InnoDB tables, it’s a bit slower but you won’t notice it unless you’re dealing with millions of records, it has transations support and binary-logging. Regarding php it doesn’t matter what kind of storage system you’re using.
If you really need to have some big big table and need it very fast and you’re not concerned about data loss you can always use myISAM and mix these table with your other InnoDB tables.
Some of our tables WILL be huge, but most will be in the low to mid tens of thousands. Since it aint broke, and I do backups nightly, I think I will just leave well enough alone.