MySQL storage engine Select InnoDB or MyISAM

MyISAM is the default storage engine in MySQL, and it's not usually a lot of people who care about this stuff. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth studying, and the article here only considers the two MyISAM and InnoDB, because these are the two most common.

Let us answer some of the following questions:

Do you have a foreign key in your database?

Do you need business support?

Do you need full-text indexing?

What kind of query mode do you often use?

How big is your data?

Thinking about these issues can help you find the right direction, but that's not absolute. If you need a transaction or a foreign key, then InnoDB may be a better way. If you need Full-text indexing, then MyISAM is usually a good choice because it's built into the system, but we don't actually test 2 million of lines of records regularly. So, even slowly, we can get full-text indexing from InnoDB by using Sphinx.

The size of the data is an important factor that affects what storage engine you choose, and large data sets tend to choose InnoDB, because they support transaction processing and failback. The size of the database determines the length of the recovery, InnoDB can use the transaction log for data recovery, which is faster. And MyISAM may take hours or even days to do these things, InnoDB only take a few minutes.

Your habit of manipulating database tables may also be a significant factor in performance impact. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. The primary key query is pretty fast under InnoDB, but it's important to be careful if our primary key is too long to cause performance problems. A large number of inserts statements would be faster under MyISAM, but updates would be quicker under innodb-especially when the volume of concurrency was high.

So, exactly which one do you use? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, using MyISAM in a large environment can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data, and require transaction processing or foreign key support, you should really use the INNODB approach directly. But remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables can be a very bad experience for you.