Difference between InnoDB and MyISAM in MySQL

[Nytro]

Difference between InnoDB and MyISAM in MySQL

Posted by Sachin Khosla, on November 15, 2010

MyISAM and InnoDB are two most commonly used storage engines of MySQL database. However, MyISAM is the default storage engine chosen by MySQL database, when creating a new table. The major differences between these two storage engines are :

InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.

InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.

InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.

Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. It is worth mentioning that a single database can have tables of different storage engines.

File structure

MyISAM stores each table on disk with three files whose names begin with same as table name. These files have different extensions to differentiate their purpose. A .frm files stores the table format, and a .MYD (MYData) file stores the data of the table. If the table has indexes then these are stored in the .MYI (MYIndex) files.

On the other hand, InnoDB tables and their indexes are stored in the tablespace, which consists of several files. That is why InnoDB tables can be very large and can store large volume of data. The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory.

Check the engines being used for existing tables

So these are the main differences between these two engines. You can specify in the query that which engine is to be used while creating the table.

    CREATE TABLE test name varchar(30) ENGINE = InnoDB;

Since MyISAM is the default engine assigned when creating a table, so you need not to specify it, if you are planning to use MyISAM. This rule holds good, if you have not changed anything in the configuration. To check the engines of already existing tables, use the following query. It will list all the existing tables which are present in the current database, with their engines specified.

    show table status;

If you want to view the status of a particular table, then use the following query,

    show table status where Name = ‘tablename’;

Hope that helps.

Stay Digified !!

Sachin Khosla

Sursa: DigiMantra » Difference between InnoDB and MyISAM in MySQL

[hammerfall]

Mersi mult de tutorial, chiar cautam asa ceva zilele trecute, dar nu am gasit nimic satisfacator. Totusi, este destul de vag. Poate cineva sa-mi explice de ce sau in ce situatii sa folosesc InnoDB in loc de MyISAM?

Din ce am inteles, MyISAM e mai rapid, iar InnoDB e mai rentabil la bazele de date mari. In rest sunt la fel? Exista altfel de diferente care afecteaza viteza, siguranta datelor (coruperea fisierelor) etc?

[Nytro]

MySQL 5.5: Storage Engine Performance Benchmark for MyISAM and InnoDB

Incepand cu MySQL v5.5, InnoDB e databse engine-ul implicit. S-a lucrat mult la el si a fost imbunatatit foarte mult. Un lucru important e ca InnoDB respecta proprietatile ACID (atomicity, consistency, isolation, durability) - ceea ce asigura ca tranzactiile sunt procesate asa cum trebuie, de exemplu exista crash recovery.

De asemenea s-a lucrat la "partea de Windows", adica au fost facute optimizari pentru Windows, se folosesc functii native Windows, de exemplu pentru alocari de memorie sau altceva, ceea ce inseamna viteza sportia.

Setarile de configurare InnoDB iti permit sa selectezi cate thread-uri sa fie folosite, cate pentru citire si cate pentru scriere, sa folosesti alocatori de memorie specifici sistemului de operare...

Diferente ar mai fi: InnoDB are suport pentru Foreign Key (important) si lock la nivel de celula, nu la nivel de tabel, dar MyISAM suporta full text search indexes.

Practic InnoDB e mai rapid.

[denjacker]

Selectarea Motorului de Cautare - Dupa definirea campurilor si modificatorilor urmeaza unul sau mai multi modificatori care specifica atribute la nivel de tabela. Dintre acestia cel mai frecvent utilizat este modificatorul ENGINE, care informeaza MySQL ce motor de stocare, sau tip de tabela sa utilizeze. In MySQL sunt disponibile o serie de motoare de stocare, fiecare cu avantajele si caracteristicile sale.

ISAM - Motorul mostenit de la versiunile anterioare.

MYISAM - Revizuire a motorului ISAM, cu suport pentru campuri cu lungime dinamica.

INNODB - Motor tranzactional conform cu ACID, cu suport pentru chei straine.

MEMORY - Motor bazat pe memorie, cu suport pentru indexuri hash.

CVS - Motor bazat pe text, pentru seturi de inregistrari CVS.

ARCHIVE - Motor cu facilitati de compresie pentru seturi de inregistrari mari.

FEDERATED - Motor pentru tabele aflate la distanta.

NDB - Motor pentru tabele grupate in clustere.

MERGE - Motor pentru tabele fuzionate.

BLACKHOLE - Motor Bitbucket.

Sunt definite, clasificate si explicate foarte bine in cartea MySQL Database Usage & Administration - Vikram Vaswani, disponibila aici !