Friday, June 25, 2010

MySQL Data Base Engines - InnoDB Vs. MyISAM

Below Error got displayed when altering one of our Database tables.

#1030 - Got error -1 from storage engine

This issue got solved when I changed the database engine of the table from InnoDB to MyISAM, and I have contacted our hosting support to find the root cause of this issue to avoid any further issues. And, waiting for their updates.

If anyone know the reason for this issue you can share your knowledge thro' the comments section.

You can continue your reading if you want to know more about these database engines.

The storage-engine is what will store, handle, and retrieve information for a particular table.

MyISAM is the default engine with MySQL. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

MyISAM and InnoDB are the two most-common database engines available with the popular open-source database provider, MySQL.

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE table option:


You can check or repair MyISAM tables with the mysqlcheck client or myisamchk utility.

MyISAM has full-text search index.

InnoDB is relatively newer than MyISAM. And it supports use of foreign-key constraints. i-e In an employee management system, foreign-key constraint can prevent deletion of any row from department table if that department is used in employee table. In other words we can not delete a department if an employee is associated with that department.

InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys.

InnoDB stores its tables and indexes in a tablespace, which may consist of several files.

InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking. i-e 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.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs.

InnoDB consumes more system resources such as RAM.

So, basically you can use MyISAM for simple databases and can go for InnoDB for relatively complex requirements.

More Articles...
You can bookmark this blog for further reading, or you can subscribe to our blog feed.

No comments:

Search This Blog