37
What are the main differences between Engines MyISAM and InnoDB and what is the most appropriate situation for the use of each?
I know that MyISAM can’t stand it FOREIGN KEY but supports FULLTEXT, while in the InnoDB is the opposite, at least until Mysql version 5.5.
Why a RDBMS (RDBDR) has as standard a engine that does not offer means to relate the tables?
42
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 that people who least care and use Mysql Innodb is not the most appropriate). Myisam is simpler, but mainly because it is what people most look for when using Mysql, is what made Mysql famous. It is preferred because in much of the simple applications where it is used, table relationships are not so important. Or it is, but it can very well be done by the application. There are those who like or need this control to be done in the database, but it is not the case of everyone, of every project.
If you need performance and simplicity you will opt for Myisam. If you need the most accurate controls, opt for Innodb. Usually you don’t have to choose him if you don’t need him, and you don’t know how to use his resources. And most small projects don’t need these resources. Some more "malicious" will say that if you need Innodb, then you better opt for another more complete database, which does more for you. It is true that Innodb leaves a little 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.
Usually when you have a lot of reading and little writing, Myisam does better. Innodb can have advantages if the amount of truly competing writing is too large. What is more rare.
Innodb can’t be faster because he does more than Myisam. Unless Myisam was very poorly implemented, he has to be faster. It doesn’t have so much scalability that it’s different from having no performance. People sometimes confuse these things. A purely database flat file would be the fastest of all at least for certain operations.
You can relate tables to Myisam. You just don’t do this in the template definition. There’s no way to make the relationship happen automatically, but you do it in your darlings usually without losing anything. When logic is more in the application than in the database it is common to prefer not to force the relationship and leave the JOIN in query take care of it. Everything has advantages and disadvantages but this is another matter.
There are even simpler and probably faster database systems. For every problem, a solution. Everything is choice. You can not do magic and have everything. Although Mariadb tried to reconcile a little more these things with the engine Aria (can be used with Mysql).
| Feature | Myisam | Innodb |
|---|---|---|
| ACID | Not | Yes |
| Configuration of ACID behaviour | Not | Yes |
| Protection when it breaks | Not | Yes* |
| Automatic foreign key | Not | Yes |
| MVCC | Not | Yes |
| Geospatial type | Yes | Yes |
| Indexing Geospatial | Yes | Not |
| Indexing Full-Text | Yes | Yes 4 |
| Data cache | Not | Yes |
| Total capacity of the bank | 256TB | 64TB |
| Occupation of disk space | Low | High |
| Occupation of memory space | Low | High |
| Locking | Table | Registering |
| Concurrent reading ability** | High | Regular |
| Concurrent writing ability** | Low | High |
| Easy backup | Not | Yes |
| COUNT(8) FAST* ** | Yes | Not |
| Data compression | reading | Reading/Recording |
*There are controversies (I’ve seen many reports that gives problems)
**Depends a lot on the actual load
***Many consider this irrelevant
Has a comparison on Wikipedia.
Innodb has changed a lot, some things that talk about performance are from 2011 or before... The tips are good but they’re kind of "", it’s just my opinion.
@Guilhermenascimento can you count to 10 faster than you count to 5? That’s what you’re saying. Myisam is and always will be faster as long as it has no transactions and other forms of guarantees. Only the smallest amount of writing he has to do makes a huge difference. There are several things that Innodb can evolve as much as it wants that won’t get faster. Unless it cuts features. When you do less, you do faster unless you are very inefficient, very even. Which is not the case for any engine same as other Dbs.
I did not say at any time that he overcame myisam, what I said is that many tests used today to show COST-BENEFIT are OUTDATED.
Good answer. I always see people criticizing the MYISAM. It’s good to see the opinion of people who balance both sides
Good response, but Mysql version 5.7.5 already has geospatial index support. Mysql Doc
@Monarchi gets that remark, because I don’t have that table anymore and I won’t do it all again :)
16
I found a very detailed explanation and would like to share, maybe I can help you.
General remarks
Innodb works faster than Myisam when there are constant modifications to the data, since this type of storage uses record protection (Row Locking) and not table protection (Locking) as does Myisam. However, in many situations, Innodb is slower than Myisam due to the fact that Innodb works with transactions. Mysql’s default storage is Myisam. Another interesting point is foreign key restrictions (Foreign key constraints) and transactions. These features are only available in Innodb. Myisam is suitable for tables whose data does not change frequently. An example of this is a table of cities and states. Since this type of table is usually 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;
Indexes and data files can be stored on different devices (increased performance);
Data distribution algorithms and improved index management;
Innodb
Run under an engine, developed by Innobase( www.innodb.com), which adds support for TRANSACTIONS and foreign key CONSTRAINTS to Mysql.
To achieve maximum performance with Innodb tables you need to configure the file my.conf(or my.ini in Windows) 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’ve helped.
I found your answer shorter and more direct, and without achism and controversies, in the rest that each do their tests and try, benchmark in comics is very subjective
@Sneepsninja show the achismos in my answer.
@bigown you’ve already quoted *There are controversies (I’ve seen many reports that gives problems) some words leave the reader to merce a lot of subjectivity example "... It’s still true." ".. Some say.." ".. others disagree." , ".. Everything has advantages and disadvantages.."
@mustache the answer this right, you responded with your experience and experience, I know it’s hard to answer without making some kind of performance comparison, but the truth is in the two answers, are different things that perform well in different environments such which have been answered in both answers
@Sneepsninja Because the subject is subjective and you said it yourself. Trying to make something subjective as if it were objective is to deceive the reader. It’s making false claims. Things are not black and white. I gave an honest answer showing that you can’t tell which is better. Other more objective parts did not need these caveats. Well, I find this answer rather limited, there is nothing wrong with it (although a sentence can be misinterpreted by those who do not understand the subject), but it reduces it to a few aspects. It may seem that these differences are all.
I agree @bigown for those who do not understand the subject some comments may lead to a misinterpretation for example ".. Innodb leaves something to be desired for some other Sgdbs.." gives the impression that Innodb is worthless, when in fact it depends a lot on the optics, if we look at the Cost x performance this would be a very unfair statement.
@Now I get it, you like Innodb and you didn’t like what I said. I could be more complete and show the numerous problems he has, comparing with other solutions. But you prefer short answers that don’t tell you anything, so I’ll leave it at that.
Let’s go continue this discussion in chat.
Browser other questions tagged mysql database innodb
You are not signed in. Login or sign up in order to post.
The
Full-text search(FTS) has been implementing in innoDB since version 5.6.4 of Mysql, however there are some restrictions: https://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html– Guilherme Nascimento