- The big difference between MySQL Table Type MyISAM and InnoDB is that InnoDB supports transaction
- InnoDB supports some newer features: Transactions, row-level locking, foreign keys
- InnoDB is for high volume, high performance
Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine.With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence.
InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.
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.
So, what do you think about those engines?? Please feel free to discuss it.
For more information visit http://dev.mysql.com/
Thanks
MyISAM is of course the default table type i'm MySQL. It's an improvement over the now-deprecated ISAM table type. It provides a simple tree structure for quick indexing on small tables, typicall fewer than 10,000 rows of data.
There are some other types of tables supported in MySQL, most of which won't provide any noticable differences.
A MERGE table is a table that only exists in memory, and will not be saved if MySQL crashes or has to be restarted. It is, as the name suggests, a merge of two (or more) tables. If you drop a MERGE table, it only drops the reference to the merge, not to the tables that are combined in it. It's not good for much!
Another table, the one which is best for huge tables, is InnoDB. It's probably the best RDBMS out there; it's actually a whole set of drivers on it's own, and when you use it, MySQL is really just a wrapper around the InnoDB table. Slashdot and other megamammoth sites out there use it, it's fast and effecient, but the trees are pretty large, so it's not good for smaller tables.
There are a few others.. like BerkeleyDB, which isn't anything special and doesn't provide any major performance increase.
The major difference between the various table types are the data tree structures and how they're handled within the RDBMS.
In the end, you'll probably find yourself sticking to MyISAM unless you're on a very large-scale project, in which cas eyou probably woudln't use MySQL anyway
