→ mysql innodb and myisam
The storage-engine is what will store, handle, and retrieve information for a particular table.
Advantages of InnoDB- InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
- Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.
Disadvantages of InnoDB
- Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
- Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
- No full-text indexing.
Advantages of MyISAM
- Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
- Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
- Full-text indexing.
- Especially good for read-intensive (select) tables.
Disadvantages of MyISAM
- No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
- Doesn’t support transactions which is essential in critical data applications such as that of banking.
- Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.
The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.
ComparisonMyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.
InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.
InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.
REMEMBER!
It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.
Extra ReadingMySQL Performance Blog - Graphically shows how InnoDB overtakes MyISAM at a certain number of queries per second.
MySQL Manual InnoDB Overview - Great overview of capabilities and limitations.
MySQL Manual MyISAM Overview - Same as above
MySQL Manual Storage Engines - Information on the other less-used storage engines MySQL offers.
blog comments powered by Disqus