Innodb or MyISAM

[RFH Reseller: infiniteimprobability]

Just started planning a new MySQL database and reading up on whether to use MyISAM or INNODB tables.

Generally seems INNODB is more reliable but then a while ago when Aluminium had a problem the INNODB tables were unrecoverable which confuses me a little - could anyone clarify whether or not INNODB is more reliable or is there some issue with INNODB in a shared server environment (i.e. is backing up and restoring INNODB tables in a shared environment not feasible?)

Thanks,

Steve.

[RFH Reseller: nick]

INNODB supports foreign key constraints and transactions, MyISAM does not. MyISAM supports fulltext searching, INNODB currently does not. MyISAM is generally accepted to be faster at selects, but slower at inserts.

I'd would have said INNODB to be the more reliable one, hard to do after the aluminium issue. I think day-to-day data integrity INNODB is better, its problem was in the way it was backed up, though I can't remember the details now?

Not entirely answered your question, but they are the things I'd base my choice on first. Happily you can have a mix of table types in the same database, so that's worth bearing in mind too.

[RFH Reseller: infiniteimprobability]

Thanks,

I just remember Warren advising us to use MyISAM and can't remember why either!

I guess MyISAM is going to meet my needs and although maybe not as reliable that doesn't mean it's unreliable.

[RFH Reseller: askjim]

try sql server - has all the features mentioned as standard :-)

i could never use a database server where referential integrity was not available

[RFH Reseller: infiniteimprobability]

Maybe so, I work on so many different databases - both desktop apps and web based that I sometimes feel I'm spreading myself too thin and need to standardise on one SQL server whether it be MySQL or MS SQL. MySQL just beats MS SQL in terms of license costs on the desktop though although I suppose SQL Express addresses that for apps with fewer users.

[RFH Reseller: nick]

i could never use a database server where referential integrity was not available

In fairness to MySQL, that's what INNODB does give you.

[RFH Reseller: askjim]

Maybe so, I work on so many different databases - both desktop apps and web based that I sometimes feel I'm spreading myself too thin and need to standardise on one SQL server whether it be MySQL or MS SQL. MySQL just beats MS SQL in terms of license costs on the desktop though although I suppose SQL Express addresses that for apps with fewer users.

SQL Express should be a good competitor to mysql - i dont think the number of users should be a problem.

[RFH Customer: Jon Freeman]

Maybe so, I work on so many different databases - both desktop apps and web based that I sometimes feel I'm spreading myself too thin and need to standardise on one SQL server whether it be MySQL or MS SQL. MySQL just beats MS SQL in terms of license costs on the desktop though although I suppose SQL Express addresses that for apps with fewer users.

An issue to me is that I use Linux at home, use Windows shared hosting and wouldn't want to commit my databases to either platform. I think MySQL wins on that.

[RFH Reseller: infiniteimprobability]

Well I've pretty much standardised on MySQL now. With the costs and great GUI tools available for Windows or Linux I can't fault it.

Yes SQL Express is free for upto 25 users I think but the deciding factor was the install cost for SQL Express - the number of components and dependencies etc. it needs make it a nightmare deploying on different PCs with different histories and service packs. It just seems too bloated, I can download MySQL and have it installed and up and running in minutes rather than hours with a minimal overhead on the PC.