On MySQL storage engine selection InnoDB or MyISAM

51CTO recommended topic: MySQL getting started and proficient in PHP + MySQL video tutorial MyISAM is the default storage engine in MySQL. Generally, not many people care about this. It is a tricky thing to decide what kind of storage engine to use, but let's take a look at it. Here we only consider MyISAM and InnoDB, because these two

51CTO recommended topic: MySQL getting started and proficient in PHP + MySQL video tutorial MyISAM is the default storage engine in MySQL. Generally, not many people care about this. It is a tricky thing to decide what kind of storage engine to use, but let's take a look at it. Here we only consider MyISAM and InnoDB, because these two

51CTO recommended topics: MySQL getting started and proficient in PHP + MySQL video tutorial

MyISAM is the default storage engine in MySQL. Generally, not many people care about this. It is a tricky thing to decide what storage engine to use, but let's take a look at it. Here we only consider MyISAM and InnoDB, because these two are the most common.

Next let's answer some questions:
◆ Does your database have a foreign key?
◆ Do you need transaction support?
◆ Do you need full-text indexing?
◆ What query mode do you often use?
◆ How big is your data?

Thinking about the above questions can help you find the right direction, but that is not absolute. If you need transaction processing or foreign keys, InnoDB may be a good method. If you need full-text indexing, MyISAM is usually a good choice because it is built in the system. However, we do not often test the 2 million rows of records. Therefore, even if it is slower, we can use Sphinx to obtain full-text indexes from InnoDB.

The size of data is an important factor that affects your choice of storage engines. Large-sized data sets tend to use InnoDB because they support transaction processing and fault recovery. The small size of the database determines the duration of fault recovery. InnoDB can use transaction logs to recover data, which is faster. However, MyISAM may take several hours or even a few days to do these tasks. InnoDB only needs a few minutes.

The habit of operating database tables may also be a factor that has a great impact on performance. For example, COUNT () can be very fast in the MyISAM table, but it may be very painful in the InnoDB table. Primary Key query will be quite fast in InnoDB, but be careful that if our primary key is too long, it will also cause performance problems. A large number of inserts statements are faster in MyISAM, but the updates is faster in InnoDB-especially when the concurrency is large.

So which one do you use? Based on experience, if it is a small application or project, MyISAM may be more suitable. Of course, using MyISAM in a large environment can also be very successful, but this is not always the case. If you plan to use a project with a large amount of data and require transaction processing or foreign key support, you should use InnoDB directly. But remember that InnoDB tables require more memory and storage. Converting a 100 GB MyISAM table to an InnoDB table may cause a bad experience.