[RESOLVED] MyISAM vs InnoDB

fr600

I'm just curious to know the difference between MyISAM and InnoDB. Are there any difference between these two storage engine, performance wise?

Thanks

godwheel

There is no generally valid answer to the question of which table type offers faster response. In principle, since transactions take time and InnoDB tables take up more space on the hard drive, MyISAM should have the advantage. But with InnoDB tables you can avoid the use of LOCK TABLE commands, which offers an advantage to InnoDB, which is better optimized for certain applications.

jara06

Basicly as godwheel explains.

"Fully integrated with MySQL Server, the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 2GB."

Taken from http://dev.mysql.com/doc/mysql/en/innodb.html
Also see http://archives.neohapsis.com/archives/mysql/2004-q2/2499.html

sfullman

If you want foreign keys, you have to go with InnoDB. I'll never go back to non-Innodb for that reason.

Just be aware you can't use any cute tricks any more like copying all the files (tables and indexes) with the same name to another location as a backup. ALL DATA is physically located in one file - so for multiple databases belonging to different accounts, there's no longer the transparent method of reading the size of that folder (their database) or allowing them to backup the contents of that folder. You have to really depend on mySQL at that point. If I could have both worlds I'd take it but relational tables are a must for me.

sam

fr600

Thanks guys, now I understand.