Last updated on September 11th, 2024 at 04:32 am
MySQL database offers two storage engines – InnoDB and MyISAM for handling your data. It is very important to pick the right storage engine for your data, depending on its structure and utility. Choosing the right storage engine can significantly boost your database performance. In this article, we will learn more about InnoDB and MyISAM storage engines and also see a detailed comparison between InnoDB vs MyISAM.
What is Storage Engine
A Storage engine is the main component of every database system. It does the heavy lifting of data creation, update, deletion and reading. Each storage engine is designed to support a specific type of application. It is important to understand the features and differences of storage engine to be able to choose the correct type of engine for your data. MySQL database supports ISAM, MyISAM, MERGE, InnoDB, MEMORY (HEAP), ARCHIVE, BDB, CSV and FEDERATED storage engines. You can get a list of storage engines supported by your database using ‘SHOW ENGINES’ query. Among them, InnoDB and MyISAM are the most popular ones and the focus of our article.
What is MyISAM
MyISAM is an old storage engine of MySQL suitable for applications that require frequent read operations and rare write operations. They are useful for data warehousing and search engines where data is mostly read and less frequently written or updated. This is because of its simple layout which involves constant row length and powerful indexing structure. It used to be the default storage engine for MySQL until 2009. But with the onset of transactional applications such as online stores, it was replaced by InnoDB database as the default engine. Nevertheless, it is great for building content management systems, search engines and text-based data warehouses.
What is InnoDB
InnoDB is a general-purpose storage engine suitable for most websites and applications built today. It provides a good balance between high performance and reliability for transactional data. It can handle large volumes of read and write operations at the same time making it suitable for Ecommerce, Social Media and Mobile Apps. It is very handy for large relational databases. InnoDB stores all the transactional data on the disk or memory for fast access. They are stored in your database only when you commit these transactions. InnoDB is the default storage engine in MySQL and great for quickly building websites, web applications and mobile apps.
InnoDB vs MyISAM : Detailed Comparison
Let us learn more about the detailed differences between InnoDB vs MyISAM.
Storage Engine
InnoDB is a transactional database that supports frequent read and write operations on large databases. It features automatic rollbacks in case of incomplete transactions or errors.
MyISAM is not suitable for transactional data or large databases. It is suitable for read-intensive applications with minimal write operations. Any rollbacks need to be done manually since it does not support automatic rollbacks.
Transactions
InnoDB was built specifically for web 2.0 applications such as online stores, bidding sites, social media, financial sites, etc. that require a large amount of both read and write operations. InnoDB stores all transactional data in a separate buffer area for easy read-write access. It is stored into database only when they are committed. It also supports automatic rollbacks in case of errors, ensuring data integrity.
MyISAM was built for an era of search engines and data warehouses and before the arrival of web 2.0 applications that are transactional in nature. So it does not support transactions. Also, you need to manually rollback changes, which can be error-prone.
Performance
MyISAM provides fast read operations but slow write operations. Also, since all its data is stored in a single file, it is suitable for small-medium databases and not large databases. It offers data compression and write delays to improve performance.
InnoDB delivers great performance for large databases with many write operations. But it is slower than MyISAM for read operations. It provides a good performance for large number of concurrent read and write operations. It offers hash indexing and performance tuning capabilities to improve database speed.
ACID Properties
InnoDB supports ACID (Atomicity, Consistency, Isolation and Durability). Sadly, MyISAM does not support any of the ACID properties.
Data Recovery
MyISAM provides limited to no crash recovery options. In face of errors or data failures, you need to manually rollback and repair data, which is very difficult.
InnoDB is designed with today’s data requirements. It provides automatic rollback option that kicks in whenever there is transaction failure or error. This helps maintain data integrity. Also, it stores all transactional changes in a buffer area and does not write to database unless they are committed.
Foreign Key
Foreign key is a database column that references another table’s primary key column. InnoDB supports foreign keys, required for relational databases, whereas MyISAM does not support foreign keys. So InnoDB is suitable for modern websites, web applications and mobile apps whereas MyISAM cannot be used for any of these.
Data Locking
MyISAM supports table-level locking where the entire table is locked for write operations. This is suitable for read-heavy operations but not good for write heavy ones. This is because table-level locking supports only one user session to perform write operations at a time while the rest have to wait for the lock to be released before making changes. Nevertheless, it requires less memory.
InnoDB uses row-level locking that locks only the row that is being updated or created, instead of the entire table. This allows multiple concurrent user sessions to work with the database at the same time. However, it requires more memory and can lead to slower read queries.
Caching & Indexing
InnoDB supports a pool of buffers that cache both data as well as indexes. MyISAM supports key buffers only for indexes.
InnoDB does not support full-text search but MyISAM not only supports it but also allows you to run complex search queries on it.
InnoDB vs MyISAM : Quick Reference
Here is a quick comparison of InnoDB vs MyISAM.
| InnoDB | MyISAM |
| Default Storage Engine since MySQL 5.6 | Not Default Storage Engine |
| Supports transactions | Does not Support transactions |
| Automatic Rollback | No Rollback |
| Supports foreign keys. Suitable for relational databases. | Does not support foreign keys. Cannot be used for relational databases |
| Row-level locking. Faster writes, slower reads than MyISAM | Table level locking. Slower writes, faster reads than MyInnoDB. |
| Great for large databases | Great for small-medium databases |
| Fast performance for mix workload of concurrent read-write operations | Fast performance for read-heavy operations |
| Does not support full-text search. | Supports Full-Text Search |
| Suitable for websites, web applications, social media, ecommerce and other transactional applications | Suitable for search engines and data warehousing. |
| Supports ACID Properties (Atomicity, Consistency, Isolation, Durability) | Does not support ACID properties |
When to use InnoDB
InnoDB is designed to support frequent read as well as write operations by concurrent users. It is also capable of handling large volumes of data. Its support foreign keys makes it best for use cases that require relational databases. Therefore, it is suitable for running today’s websites, web applications, social media sites, financial applications and mobile apps that require large number of transactions on a daily basis. InnoDB has been designed with today’s growing needs for transactional databases.
When to use MyISAM
MyISAM is suitable for read-heavy applications such as search engines that require less frequent write operations. But its lack of support for foreign keys makes it unsuitable for relational databases. So it automatically becomes unsuitable for websites and apps. It was built during the search engine era.
How to Determine Storage Engine
You can easily find out the storage engine by running the following query for your table. Log into MySQL database, and run the following command to use the table’s database.
USE database_name
Then run the following command to replace table_name with the name of your table.
SHOW TABLE STATUS WHERE Name = 'table_name'
Here is an example. The output will contain many columns including Engine, which displays engine name.
mysql> show table status where name='sales';
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| sales | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 8388608 | NULL | 2023-10-18 13:49:49 | NULL | NULL | latin1_swedish_ci | NULL | | |
Alternatively, you can also use the following statement.
SHOW CREATE TABLE database_name.table_name
Here is a sample output with storage engine highlighted in bold.
mysql> SHOW CREATE TABLE sample.sales;
+---------------+
| Table | Create Table
+---------------+
| sales | CREATE TABLE `sales` (
`id` int(11) NOT NULL,
`order_date` date DEFAULT NULL,
`amount` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+
How to Convert MyISAM Tables to InnoDB
If you want to change the storage engine of one table from MyISAM to InnoDB, you can run the following query.
ALTER TABLE database_name.table_name ENGINE=InnoDB;
Here is an example to change storage engine of table sales.
ALTER TABLE sample.sales ENGINE=InnoDB;
How to Convert InnoDB to MyISAM
If you want to change the storage engine of one table from InnoDB to MyISAM, you can run the following query.
ALTER TABLE database_name.table_name ENGINE=MuISAM;
Here is an example to change storage engine of table sales.
ALTER TABLE sample.sales ENGINE=MyISAM;
Conclusion
In this article, we have learnt about InnoDB and MyISAM storage engines in MySQL. It is important to choose the right storage for your website or application, to get a good performance. InnoDB is suitable for transactional database, with a mixed workload of many concurrent read-write transactions. Therefore it is appropriate for websites, online stores, social media sites and mobile apps. On the other hand, MyISAM is suitable for search engines and similar read-heavy applications. Depending on your requirement, you can choose either of these storage engines.
Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Also read:
Single Quotes, Double Quotes and Backticks in MySQL
Difference Between Inner Join & Outer Join
How to Do Outer Join in MySQL
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.