A database engine/storage engine is the underlying software component that a DBMS uses to create, read, update and delete data from a database.

Most of us are aware about the storage engines commonly used in Mysql: MyIsam and InnoDB

The main differences between InnoDB and MyISAM are support for “referential integrity” and “transactions”.

If you need the database to enforce foreign key constraints, or you need the database to support transactions

Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

To summarize the advantages of MyIsam and InnoDB, please see

MYISAM:

MYISAM supports Table-level Locking

MyISAM designed for need for speed

MyISAM does not support foreign keys, hence we call MySQL with MYISAM is a DBMS

MyISAM stores its tables, data and indexes indiskspaceusing separate three different files. (tablename. FRM, tablename. MYD, tablename. MYI)

MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command, it’s done.

MYISAM supports full text search

You can use MyISAM, if the table is more static with lots of selections and less update and delete.

INNODB:

InnoDB supports Row-level Locking

InnoDB designed for maximum performance when processing high volume of data

InnoDB support foreign keys, hence we call MySQL with InnoDB is an RDBMS

InnoDB stores its tables and indexes in a tablespace

InnoDB supports transaction. You can commit and rollback with InnoDB

Here I would like to go for few solutions that come across while repairing MyIsam engine.

When you get the following error when repairing the database

Error code:

Error:Incorrect key file for table ‘./DB/table’; try to repair it

Error :Incorrect key file for table ‘DB_table’; try to repair it

You can try the following steps for correcting this:

Step 1. Stop mysql service

Step 2. Rename the.myifile to something else, like “DISK.BAD”.

mv/var/lib/mysql/database/*.MYI /root/database_MYI/

Step 3. Restart mysql

Step 4. REPAIR TABLE — MySQL will see that the MYI file is missing and rebuild it. But this time, it will be in a different spot on the disk.

foriin`cat/root/tbs`;domysql -e “REPAIR TABLE database.$i USE_FRM;”;done

If you are gettingerrorlike Can’t create new tempfile: ‘tablesname. TMD file error while repairs corrupted database tables

Please try using following command to fix it.

Error code:

error   :Can’t create new tempfile: ‘./DB/table.TMD’

status  :Operation failed

Solutions:   myisamchk-r -v -f  tables.MYI