InnoDB : It is a transaction-safe table that is managed by the InnoDB handler. As a result, data is not stored in a .MYD file, but instead is manged in the InnoDB tablespace. InnoDB tables also support full foreign key functionality in MySQL, unlike other tables. In addition, the InnoDB handler supports automatic recovery and row-level locking. InnoDB tables do not perform as well as MyISAM tables.
MyISAM: It is default table type in MySQL. It supports extensive indexing and are optimized for compression and sped. It also supports backward compatibility. Unlike other tables types, BLOB and TEXT columns can be indexed and null values are allowed in indexed columns. MyISAM tables are not transaction-safe, and they do not support full foreign key functionality.
Which table type should be used when huge amount of update query has to perform?
When need to update a table multiple times, InnoDB is preferred. Because, it do row-level locking. i.e. only one row will be lock on which updation is going on.. While MyISAM do table-level lockin i.e. whenever an update query is going to execute on a table, whole table will be locked. So, other queries which have to make operation on that table, need to wait untill the table has been unblocked. MyISAM is faster than InnoDB. So, this table type should be used in those cases where multiple queries is executing to fetch the data from the table only. For example, applications such as blog need to show the data only.
No comments:
Post a Comment
Thanks for your valuable comments.