InnoDB and MyISAM are two of the most widely used MySQL storage engines. Each of them has its own pros and cons – we will try to go over them in this article.
Briefly about MyISAM
MyISAM was a storage engine used in MySQL prior to version 5.5 which was released in December 2009. MyISAM is based on an old ISAM storage engine which is no longer available as of MySQL 3.23 – the Indexed Sequential Access Method engine was succeeded by MyISAM. MyISAM was introduced into MySQL earlier than InnoDB.
MyISAM Features
MyISAM comes with the following features:
- MyISAM supports table-level locking – this is one of the primary reasons why you will see MyISAM being frequently chosen as a more suitable storage engine for read operations. This type of locking also avoids deadlocks by always requesting all locks when the query begins and always locking tables in the same order. The main advantages of table-level locking is that it requires relatively little memory in comparison to row-level locking and it’s reasonably fast if you need to frequently scan the whole table;
- MyISAM supports full-text indexes by default – InnoDB started supporting full-text indexes only when MySQL 5.6 was released;
- MyISAM has some index advantages – when using MyISAM, you can create indexes on the first 500 characters of
BLOBandTEXTcolumns; - MyISAM storage engine utilizes less disk footprint than InnoDB – MyISAM utilizes two or three times less disk space than InnoDB. This is debatably the only advantage of the engine;
- Simpler design – MyISAM’s design is more simple compared to InnoDB.
Briefly about InnoDB
InnoDB is a general-purpose MySQL storage engine and a default storage engine as of MySQL 5.6. The engine provides ACID-compliant transaction features, supports foreign keys, balances high reliability with high performance and is the focus of the majority of developers working with MySQL.
InnoDB Features
InnoDB comes with the following features:
- InnoDB supports row-level locking – differently from MyISAM, InnoDB supports row-level locking meaning that only the row that is accessed by an application will be locked. This type of locking makes it possible to lock a single row for long periods of time and produces fewer lock conflicts when accessing different rows in many threads;
- The main mechanism used is the InnoDB buffer pool – the buffer pool caches data and index pages from InnoDB tables;
- InnoDB supports foreign keys – foreign keys allow developers to let the database ensure the integrity of its state and relationships between tables;
- InnoDB is ACID compliant – InnoDB provides the standard ACID-compliant transaction features.
Should you choose MyISAM or InnoDB?
Working with MyISAM is widely discouraged since the engine is non-transactional, uses table-level locks and has no crash recovery. Moreover, InnoDB has implemented most of the missing features:
| Locking | Yes, table-level | Yes, row-level |
| Full-text Search | Yes | Yes, since MySQL 5.6.4 |
| Last Update for Table | Yes | Yes, since MySQL 5.7 |
| Crash Recovery | No | Yes |
| Index Cardinality is Stored in Tables | Yes | No |
In general, it is widely accepted that you should always use InnoDB when dealing with any data that perceives value. Working with MyISAM is harder (for example, you cannot just copy MyISAM tables into a MySQL server, but you can do that in InnoDB using ALTER TABLE ... IMPORT TABLESPACE;) and the only scenario when MyISAM is still faster than InnoDB is when running COUNT(*) queries because MyISAM stores the number of rows in the table metadata. Such queries on InnoDB are slower as InnoDB does not have such counter and the engine needs to perform a full table scan or a full index scan.
Moreover, if you’re using both InnoDB and MyISAM it will be hard to optimize performance for both of the engines at once. The following table depicts the list of available MyISAM parameters:
| key_buffer_size | This parameter is used to cache index blocks for MyISAM tables. |
| read_buffer_size | The size of the buffer used for full table scans of MyISAM tables. |
| read_rnd_buffer_size | The rows are read through this buffer when reading rows in a sorted order after performing sort operations. Reading through this buffer avoids disk seeks – ORDER BY performance can be improved if the value of this variable is set to a high value. |
| bulk_insert_buffer_size | Used to make bulk inserts faster – the value of this buffer is allocated when a bulk insert is detected. Set to value of 0 to disable. |
| myisam_sort_buffer_size | The value of this buffer is allocated when the MyISAM storage engine needs to rebuild indexes. Used when the REPAIR TABLE, OPTIMIZE TABLE, ALTER TABLE and LOAD DATA INFILE statements are executed. Allocated on a per-thread basis. |
| myisam_max_sort_file_size | The maximum size of a temporary file that will be used while recreating indexes during the processing of REPAIR TABLE, OPTIMIZE TABLE, ALTER TABLE and LOAD DATA INFILE statements. If the size of the temporary file is bigger than the value of this parameter, indexes will be created using the key cache. |
| myisam_repair_threads | If a table has more than one index, the MyISAM storage engine will use more than one thread to repair the indexes by sorting in parallel. |
| myisam_recover | Allows automatic repair and recovery of MyISAM tables that were not closed properly. |
If you’re running InnoDB on the other hand, you would need to tune the following parameters:
| innodb_data_file_path | Refers to the file where data derived from InnoDB tables will be stored. By default the file is ibdata1 in the /var/lib/mysql directory – the ibdata1 is the system tablespace for the InnoDB infrastructure. |
| innodb_buffer_pool_size | The memory buffer that InnoDB uses to cache data and indexes of its tables. |
| innodb_log_file_size | Contains the size of InnoDB log files. The larger this value is, the less recovery time you need in case of a crash. |
| innodb_log_buffer_size | InnoDB uses this value to write to the log files on disk. |
| innodb_flush_log_at_trx_commit | Controls the balance between strict ACID compliance for commit operations and higher performance. Changing the default value of this parameter gets a very fast write speed, but can lose up to one seconds worth of transactions and vice versa. |
| innodb_lock_wait_timeout | The length of time in seconds an InnoDB transaction waits for a row lock before giving up. |
| innodb_flush_method | Defines the method used to flush data to InnoDB data files and log files. |
Summary
While there is nothing wrong with using multiple storage engines on the same MySQL server, you would need to keep in mind that both MyISAM’s and InnoDB’s parameters play a key role in the performance of each of the engines in question.
If you plan to use both of the engines at once, you would need to take proactive steps to ensure that the parameters of MyISAM (key_buffer_size etc.) and the parameters of InnoDB (innodb_buffer_pool_size etc.) are not clashing.
If you’re not mixing both of the engines together though, you should probably choose InnoDB – as stated above, working with MyISAM is widely frowned upon since the engine is non-transactional, uses table-level locks and has no crash recovery.