What are the differences between MyISAM and InnoDB?

31

What are the main differences between engines MyISAM and InnoDB and what is the most appropriate situation for each?

I know that MyISAM does not support FOREIGN KEY but supports FULLTEXT , while InnoDB is the opposite, at least until version 5.5 of MySQL.

Why does a RDBMS default to an engine that does not provide the means to relate the tables?

    
asked by Thomas 24.11.2014 в 20:15

2 answers

35

Default

This is the default because it is the one that gives the most performance (but soon the next version is no longer, and it is curious because for most scenarios people who care less and use MySQL InnoDB is not the most appropriate). MyISAM is simpler, but mostly because it's what most people are looking for when using MySQL, that's what made MySQL famous. It's preferred because in most of the simple applications where it's used, table relationship is not as important. Or it even is, but it may very well be done by the application. There are those who like or need this control to be done in the database, but not everyone, of any project.

When to use

If you need performance and simplicity, choose MyISAM. If you need the most accurate controls, choose InnoDB. In general you do not have to opt for it if you do not need it, and do not know how to use it. And most small projects do not need these features. Some more "malicious" will say that if you need InnoDB, then it is better to opt for another more complete database, which does more for you. It remains true that InnoDB leaves a bit to be desired for some other SGDBs. Some even say that MySQL is only a valid choice if you use MyISAM. Of course, others disagree.

In general, when you have a lot of reading and poor typing, MyISAM usually works out better. InnoDB can have advantages if the amount of truly competing writing is too large. What is more rare.

InnoDB can not be faster because it does more things than MyISAM. Unless MyISAM is poorly implemented, it has to be faster. It does not have as much scalability as it is different from not having performance. People sometimes confuse these things. A purely flat file database would be the fastest of all at least for certain operations.

Relationship

You can relate tables with MyISAM. You just do not do this in the model definition. There is no way to force the relationship to happen automatically, but do this in your queries normally, without losing anything. When the logic is more in the application than in the database it is common to prefer not to force the relationship and let JOIN in the query handle this. Everything has advantages and disadvantages but this is another matter.

Tradeoffs

There are even simpler and probably faster database systems. For each problem, a solution. Everything is choices. You can not do magic and have it all. Although MariaDB tried to reconcile these things a bit more with the engine Aria ( give it to use with MySQL).

Comparative

*Therearecontroversies(I'veseenmanyreportsthatgiveproblems)
**Itdependsalotontheactualload
***Manyconsiderthistobeirrelevant

Youhavea Wikipedia comparison .

    
24.11.2014 / 20:32
13

I found a very detailed explanation and would like to share, maybe I can help you.

General comments

  

InnoDB works faster than MyISAM when there are constant modifications to the data, since this type of storage uses row locking and not table locking as MyISAM does, in several situations, InnoDB is slower than MyISAM, due to the fact that InnoDB works with transactions. The default storage for MySQL is MyISAM. Another interesting point is the foreign key constraints and transactions. These features are available only in InnoDB.   MyISAM is suitable for tables whose data do not change frequently. An example of this is a table of cities and states. Since this type of table is generally used only as queries, there is no need to use InnoDB.

MyISAM

  

It was implemented from the ISAM table code, introducing several improvements such as:

     
    

Table files are transportable between different operating systems;     BLOB and TEXT fields can be indexed;

         

Index and data files can be stored on different devices (performance enhancement);

         

Data distribution algorithms and improved index management;

  

InnoDB

  
    

They run under an engine, developed by Innobase (www.innodb.com), which adds support for foreign key TRANSFERS and CONSTRAINTS to MySQL.

         

For maximum performance with InnoDB tables it is necessary to configure the my.conf (or my.ini on Windows) file according to the hardware characteristics of the server where MySQL is running. Details of this configuration can be found in the InnoDB manual.

  

For more details click here

I hope I have helped.

    
24.11.2014 в 20:31