minte9
SoftwareDevelopment / MYSQL





MyISAM tables

Only one write could be in progress at any time. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes. Text/Blob fields are able to be fully-indexed which is of great importance to search functions.

InnoDB tables

Are transaction-safe meaning data-integrity is maintained throughout the entire query process. Provides row-locking, as opposed to table-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance. Another great feature InnoDB tables is the ability to use foreign-key constraints. Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Default engine

The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5)

Check version 

 
show variables like "%version%";

Convert to InnoDB 

 
ALTER TABLE t1 ENGINE=InnoDB;
http://stackoverflow.com/questions/20148/myisam-versus-innodb https://dev.mysql.com/doc/refman/5.5/en/storage-engine-setting.html http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html





Questions and answers:
Clink on Option to Answer




1. Which engine provides row-locking

  • a) InnoDB
  • b) MyISAM

2. Which engine provides foreign-key feature?

  • a) InnoDB
  • b) MyISAM

3. Can you use fully-indexed text/blob fields with InnoDB?

  • a) yes
  • b) no

4. Convert to InnoDB ...

  • a) ALTER TABLE ... ENGINE=InnoDB
  • b) ALTER TABLE ... SET ENGINE TO 'InnoDB'


References: