Table of contents of the article:
If you are looking to improve the performance of MySQL databases in your software, you may need to know all the differences between InnoDB and MyISAM, two well-known types of MySQL storage engines. And if you're going to pick one, we believe you'd like to find out what each of them has to offer and which one fits your software best.
Every technical aspect determines the success of a project. With the growing need to archive all data, choosing a database that can successfully control all archiving requirements even in the long term is of the utmost importance. When a Solution Architect is presented with the responsibility of choosing a database, he compares those databases to each other and chooses the one that fits best.
In this blog we will talk about the two most popular storage engines of MySQL databases: InnoDB and MyISAM. We will discuss what they are and understand MyISAM vs InnoDB by comparing them with 7 critical factors.
Understanding what MySQL is
MySQL is a popular open source RDBMS (Relational Database Management System)
It is widely used by academics and professionals around the world. MySQL is available for free under the GNU public license and is also known as a premium proprietary version. Michael Widenius originally developed MySQL at MySQL AB, a company based in Sweden. In 2012, Sun Microsystems acquired MySQL AB and later Oracle acquired Sun Microsystems.
MySQL is used for various applications and is based on Structured Query Language (SQL). It is also used by some of the popular websites, including Twitter, Facebook, Mediawiki, YouTube and Flickr, etc.
MySQL has had many forks of which the most popular are MariaDB and Percona Server.
Understanding the main features of MySQL
- Ease of use: MySQL is easy to download, install and easy to use. You can simply go to the downloads from the official website and follow the detailed instructions provided in their documentation.
- Scalability: MySQL has a scalable architecture and provides high performance fast loading utilities with different memory caches.
- Compatibility: MySQL is compatible and can be installed on all modern platforms such as Windows, Linux, Unix.
- Data security: MySQL has all the features that guarantee database security and allows access only to authorized users.
- Low cost: it's free. Anyone from all over the world can download MySQL for personal use for free.
What is MyISAM?
MyISAM is short for My Indexed Sequential Access Method; MyISAM is the default storage system and is often used in web, data warehousing and other analytics environments. The MyISAM storage engine supports all MySQL configurations.
MyISAM was a default storage engine until December 2009; later, InnoDB replaced the default storage engine. MyISAM is based on the ISAM algorithm which displays the result of larger data sets faster. MyISAM has a minimal data footprint and therefore is best suited for data warehousing and web applications.
What is InnoDB?
InnoDB is a secure transaction (ACID compliant) storage engine for MySQL with features such as Commit, Rollback and Crash Recovery capabilities to protect user data and provide fault tolerance. InnoDB uses row-level locking and Oracle-style consistent non-locking on reads increases concurrency and multi-user performance.
InnoDB stores user data in clustered indexes based on primary keys to reduce I / O for common queries. To maintain data integrity, InnoDB also supports FOREIGN KEY referential integrity constraints.
With the introduction of MySQL 5.5, InnoDB has become the default storage engine and is best suited for large datasets containing structured and relational data. InnoDB focuses more on reliability and performance, making it great for content management systems.
MyISAM vs. InnoDB
Now that we have a basic understanding of MyISAM and InnoDB, let's compare them on various factors:
- MyISAM vs InnoDB: type of storage engine
- MyISAM vs InnoDB: block
- MyISAM vs InnoDB: foreign keys and referential integrity
- MyISAM vs InnoDB: ACID property
- MyISAM vs InnoDB: performance
- MyISAM vs InnoDB: reliability
- MyISAM vs InnoDB: caching and indexing
1. MyISAM vs InnoDBstorage: type of engine
MyISAM it is a type of non-transactional storage and any write options must be manually restored (if necessary).
InnoDB is a type of transaction storage that automatically rolls back writes if they are not completed.
2. MyISAM vs InnoDB storage: locking
Locking is the mechanism in MySQL that prevents two users from editing duplicate rows at the same time by locking the row. Users cannot edit the table when locking is enabled.
MyISAM uses the default method of table locking and allows a single session to modify the table. This means that only one user can edit the table at a time. If another user tries to change the table, he will get a message saying it is locked. The table locking method is useful for read-only databases as it does not require a lot of memory.
InnoDB uses row-level locking of the table. This method supports multiple sessions on the same line by locking only the lines in the editing process. Row locking is useful for databases with multiple users.
The only drawback of row-level locking is that it consumes a lot of memory, and querying and editing data takes time.
When a query is run on a MyISAM table, the entire table in which the query is run will be locked. This means that subsequent queries will only run after the current one is finished. If you are reading a large table and / or there are frequent reads and writes, this can mean a huge query backlog.
When a query is run on an InnoDB table, only the affected rows are locked, the rest of the table remains available for other operations. This means that queries can run concurrently on the same table, as long as they don't use the same row.
3. MyISAM vs InnoDB storage: foreign keys or foreign keys and referential integrity.
A foreign key is a column in a table that links data to another table. Prevents users from adding records that break the link between two tables.
MyISAM does not support the Foreign Key option.
InnoDB supports the Foreign Key option.
Referential integrity ensures that relationships between tables remain consistent. Or more generally a table has a foreign key that points to a different table. When a change is made to the bulleted table, the changes will be restricted / linked to the link table as well.
InnoDB is a relational DBMS (RDBMS) and therefore has referential integrity, while MyISAM does not. Thus InnoDB supports foreign keys and referential integrity, including cascading deletions and updates, but MyISAM does not support foreign key constraints.
So when you're designing a MySQL database and need to define integrity constraints between tables, InnoDB is your cup of coffee. (Of course, you can have the MyISAM engine for tables that you don't need to define any referential integrity constraints.)
4. MyISAM vs InnoDB storage: ACID properties
ACID stands for Atomicity, Consistency, Isolation and Durability. MyISAM does not support ACID properties while InnoDB supports ACID properties.
Data in a table is handled using Data Manipulation Language (DML) statements, such as SELECT, INSERT, UPDATE, and DELETE. A transaction groups two or more DML statements into a single unit of work, so the whole unit or none is applied.
MyISAM does not support transactions while InnoDB does.
Therefore, if a table uses the MyISAM engine and the operation is aborted, the operation is aborted immediately and the affected rows (or even the data within each row) remain affected, even if the operation has not completed. . If a table uses the InnoDB engine and the operation is aborted, as it uses transactions that have atomicity, any transactions that have not completed will have no effect as no commit is made.
When you perform an operation in MyISAM, the changes are set and you cannot revert the changes while in InnoDB, you can roll back those changes.
When you are working on an application that uses a lot of transactions, InnoDB would be a better option than MyISAM.
InnoDB also provides automatic recovery after a crash of the MySQL server or the host on which the server is running.
5. MyISAM vs InnoDB storage: performance
InnoDB supports transactional properties, ie rollback and commit, and has a higher write speed. The performance of InnoDB for large volumes of data is better than MyISAM.
MyISAM it does not support transactional properties and is faster to read. Compared to InnoDB, the performance for a high volume of data is lower.
6. MyISAM vs InnoDB storage: reliability
InnoDB it uses a transactional log to record each operation and thus provides reliable operations. Therefore, in the event of an error, the data can be recovered quickly using those logs.
MyISAM does not offer data integrity; hardware failures and undone operations can cause data corruption.
7. MyISAM vs InnoDB storage: caching and indexing
InnoDB does not support full-text indexing until MySQL version 5.6.4. Although it is an old version, some applications still use the same or older versions of MySQL (especially shared hosting providers). However, this is not a valid reason to use MyISAM. It's best to switch to a hosting provider that supports updated versions of MySQL like ours.
Also, a MyISAM table that uses FULLTEXT indexing cannot be converted to an InnoDB table.
How to check if you are using MyISAM or InnoDB
If you don't know which storage engine is currently used in your MySQL database, let's check it and see. The first way is to start MySQL Shell and run the command, then locate and select the required database with the command.SHOW DATABASES;USE database_name;
Next, a confirmation message will be shown that the database is found. You can now use the SHOW CREATE TABLE command to view information about the table and the storage engine. It looks like this:
SHOW CREATE TABLE database_name.table_name;
The output will show the default storage engine for the requested table.
There is an easier way to do this if you are using PhpMyAdmin for MySQL, just look at the various tables.
When is it best to use MyISAM and when you should use InnoDB
If a MyISAM table has a problem, it will only affect the table. If problems occur with other tables or databases, they will not affect the operations of other tables or databases. Therefore, MyISAM can be recommended for servers with multiple sites.
The InnoDB storage engine is recommended for frequent table operations. It has better performance, better functionality, and can also substantially reduce memory usage on the server. Of course, it is generally preferred over MyISAM. However, it is worth noting that since problems with InnoDB tables can cause data loss in other InnoDB tables, it is important to make sure that you have enabled automatic backups.
Going beyond mere theoretical or academic logic for use in a web context such as use in a WordPress hosting, or an ecommerce such as Prestashop or Magento, it is practically always recommended to use InnoDB instead of MyISAM.
How to convert MyISAM to InnoDB
Converting MyISAM to InnoDB can be quite useful if, for example, you encounter a previous MyISAM table that needs to be converted to match your current InnoDB environment.
How to convert all MyISAM tables to InnoDB
If you want to convert all tables into a required database, you can do it safely via phpMyAdmin.
1. Once the required database is started and selected, you can run the following query on it, after replacing database_name with the actual database name:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'database_name';
For example, this is what it will look like with the sakila database:
2. After submitting this query, you will see the list of all tables to convert to InnoDB.
3. Click + Options above the results, select Complete texts and click Plan your trip in the lower right corner of the screen.
4. Select the checkbox Show all and copy all queries using Copy negli appunti in the box Operations on query results .
5. Paste the results into the text editor and copy all lines starting with ALTER TABLE to the clipboard.
6. Click the SQL tab above the results and paste the ALTER TABLE statements into the text field, then click again Go . All tables in your database will be converted to InnoDB.
How to convert a single MyISAM table to InnoDB
If you want to convert a single table from MyISAM to InnoDB, run the ALTER TABLE command, after replacing database_name e table_name with the actual database and table names:
ALTER TABLE database_name.table_name ENGINE=InnoDB;
Conclusion
This article has listed the critical differences between InnoDB and MyISAM by comparing them with critical factors. It is important to be aware of the properties of each database as it becomes easier to make a choice. You should be aware of the similarities they possess and also what differentiates them. Hopefully after reading this article you have a better understanding of InnoDB and MyISAM.
If as we imagine you are in this blog as you are working with CMS or popular ecommerce platforms such as WordPress / Drupal / Joomla / Prestashop or Magento, the most dispassionate advice we can give you to improve the performance of your site is to convert all the MyISAM tables. to InnoDB.
If you encounter difficulties or would like to delegate an operation that is cumbersome or dangerous at first sight, please contact us.
