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 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 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: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”.
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 :Can’t create new tempfile: ‘./DB/table.TMD’
status :Operation failed
Solutions: myisamchk-r -v -f tables.MYI