Differences between MyISAM and InnoDB Based on MySQL

Reference: InnoDB or MyISAM talk about MySQL storage engine selection MYSQL: InnoDB or MyISAM?

 

MyISAM: This is the default type. It is based on the traditional isam type. isam is the abbreviation of indexed sequential access method (sequential access method with indexes, it is a standard method for storing records and files. compared with other storage engines, MyISAM has most tools for checking and repairing tables. myISAM tables can be compressed and support full-text search. they are not transaction-safe and do not support foreign keys. If a transaction is rolled back, incomplete rollback is not atomic. If you execute a large number of select statements, MyISAM is a better choice.

 

InnoDB: This type is transaction-safe. it has the same features as the bdb type and supports foreign keys. the InnoDB table is fast. it has more features than bdb. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of insert or update operations, InnoDB tables should be used for performance considerations,

 

The main difference between the two types is that InnoDB supports transaction processing and Foreign keys and row-level locks, but MyISAM does not. Therefore, MyISAM is often considered only suitable for small projects.

 

How to determine which type to use:

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.