Move left to right: InnoDB or MyISAM

Welcome to Personal original website: http://www.phpthinking.com/archives/456

MyISAM is the default storage engine in MySQL, and generally not too many people are concerned about this thing. Deciding what kind of storage engine to use is a very tricky thing to do, but it's worth it to study, the article here only considers the two MyISAM and InnoDB, because these two are the most common.

Let's answer some questions first:

• Do you have a foreign key in your database?
• Do you need business support?
• Do you need full-text indexing?
• What query patterns do you often use?
• How big is your data?

Thinking about these questions can help you find the right direction, but that's not absolute. If you need transaction processing or foreign keys, then InnoDB may be a good way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.

The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.

Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.

So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments 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 transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.

Move left to right: InnoDB or MyISAM