InnoDB vs MyISAM
Okay so I'm in a dilemma for choosing which table type to use... Both have features I want...
On the one hand MyISAM supports FULL-TEXT Searches which are very useful.
On the other hand InnoDB tables allow for TRANSACTIONAL coding
(The locking of table rows during inserts, updates, and deletes and being able to rollback changes if not all of the sql queries are executed)
What would you recommend and why?
Dont take this as expert advice !
I allways chose MyISAM coz I readed it is wery fast and can be repaired.
As folllowed from that statement inodb is 'slow' and can't be repaired.
another thing to consider is data integrity, myisam does not support foreign keys.
note that you can mix and match table types as needed. If you don't need full text indexing on the majority of your tables make them innodb type and split off what you need into myisam to take advantage of full text indexes.
As for repairs, if you are running a live production server and aren't running daily cron job to back up your data you have more things to worry about than whether or not you can run a repair against a table.
The database will be ran on a SAM system that is raided (so there will be an active backup) and then all databases will be backed up daily as a whole with a backup program.
I'd really rather not mix the table types for the sake of sanity but I've thought about that too.
Note about repair:
I had a crontab job doing backup sqldump each day, by pure luck I looked one day and see the backup file was extremly small, i.e. one table corrupted, and nothing has been backed up, doh file was created.
Hence I think repair could be useful.
Thanks for the replies... hopefully in the future they advance the abilities of these tables to incorporate all of the features into one table type.
RAID is not a backup. But if your SAN is being backed up onto tape (or similar) everyday, that should be sufficient.
Originally Posted by Coyote6
I was referring to it as more of a fail safe back up. It is writing an exact copy of the data to a separate hard drive(s)... Yes if something corrupts the data the data will be corrupted on both drives but in a case of a hard drive failure it can be considered a type of back up to the current data... but yes it is also being backed up to another source nightly.
And oops I meant SAN system not SAM.
Yes a repair could be useful, but you are more likely to need TRANSACTIONS and FOREIGN KEYs enforced, neither of which are supported by MyISAM tables.
Originally Posted by BubikolRamios
So what you are going to gain by being able to do a repair, which may crop up once in a very little while, or lose two major components, which on a real system you are to use daily if not more often.
Which do you think is more vital?
MYISAM OR innoDB
MyISAM OR InnoDB
Required full text Search myISAM
Require Transactions innoDB
frequent select queries myISAM
frequent insert,update,delete innoDB
Row Locking (multi processing on single table) innoDB
Relational base design innoDB
Hi, I have briefly discuss this matter by table so you can conclude which has to be chosen either innodb or MyISAM.