What is the difference between MySQL database table MyISAM and InnoDB type?Compare which one is better

• On MySQL When creating a table in , you can choose a storage engine.
• There are several different storage engines, but the most commonly used are MyISAM and InnoDB, they are all different MySQL Version of the default storage engine.
• If no storage engine is specified when the table is created, the MySQL version's default engine is used.
• In versions prior to MySQL 5.5.5, MyISAM was the default, but in versions after 5.5.5, InnoDB was the default.

MySQL databaseThe difference between MyISAM type and InnoDB type

• InnoDB is newer, MyISAM is older.
• InnoDB is more complex, while MyISAM is simpler.
• InnoDB is stricter about data integrity, while MyISAM is more lenient.
• InnoDB implements row-level locking for inserts and updates, while MyISAM implements table-level locking.
• InnoDB has transactions, MyISAM doesn't.
• InnoDB has foreign key and relational restrictions, while MyISAM does not.
• InnoDB has better crash resilience, while MyISAM cannot restore data integrity in the event of a system crash.
• MyISAM has full-text search indexes, while InnoDB does not.

InnoDB type advantages

InnoDB should prioritize data integrity because it handles data integrity through relational constraints and transactions.

Faster in write-intensive (insert, update) tables because it utilizes row-level locking and only retains changes to the same row that was inserted or updated.

InnoDB Type Disadvantages

• Because InnoDB handles relationships between different tables, database administrators and schema creators need to spend more time designing more complex data models than MyISAM.
• Consume more system resources such as RAM.
• In fact, many people recommend turning off the InnoDB engine after installing MySQL if you don't need it.
• No full text index

MyISAM Advantages

• It is simpler to design and create, so it is more suitable for beginners.
• Don't worry about external relationships between tables.
• Overall faster than InnoDB due to simpler structure and lower server resource cost.
• Full text index.
• Especially useful for read-intensive (select) tables.

MyISAM Type Disadvantages

• There are no data integrity (eg, relational constraints) checks, which increases responsibility and overhead for database administrators and application developers.
• Transactions that are essential in data-critical applications like banking are not supported.
• It is slower than InnoDB for frequently inserted or updated tables because the entire table is locked for any inserts or updates.

MyISAM type vs. InnoDB type, which one is better?

InnoDB is better suited for data critical situations that require frequent inserts and updates.

On the other hand, MyISAM performs better in applications that don't rely too much on data integrity, often only selecting and displaying data.

1. If you need to support transactions, choose InnoDB, and choose MyISAM if you don't need transactions.
2. If most of the table operations are queries, choose MyISAM, and choose InnoDB for read and write.
3. Do not choose MyISAM if a system crash makes data recovery difficult and expensive.

one useWordPress websiteA netizen, one day, accidentally discovered that the database is quite large, but this website has less than 10 articles, such a large database is meaningless.

Then start looking for the reason and findphpMyAdminThe backend database type is different from other WordPress sites.

This site is of type InnoDB, while other WordPress sites are of type MyISAM.

InnoDB type will cause the database size to expand several times, so netizens decided to convert from InnoDB type to MyISAM type 

Click the link below to learn how phpMyAdmin converts the InnoDB data table type to MyISAM default engine▼

How does phpMyAdmin convert InnoDB data table type conversion to MyISAM default engine?

How does phpMyAdmin convert InnoDB data table type conversion to MyISAM default engine?It is recommended to convert InnoDB data tables to MyISAM data tables: Combined with the characteristics of personal blogs, it is recommended to use MyISAM for personal blog systems, because the main operation in blogs is to read...