One of the decisions that the data base administrator must make in the implementation of a database on MySQL is the type of storage engines to choose for each table. The two main alternatives are InnoDB and MyISAM. This post highlights the advantages and disadvantages of these storage engines.
Differences between InnoDB and MyISAM
InnoDB has become the default storage engine in the last versions of MySQL. The functionalities offered by InnoDB are preferrable in many cases. But in special cases some of the characteristics of MyISAM can be better suited to the type of content or usage of a given table.
InnoDB specific functionalities
- ACID transactions
- record-level locking (MyISAM only has table-level locking)
- foreign key constraints
- automatic crash recovery
- compression of tables, that remain as read/write (MyISAM has table compression, but read-only)
- spatial data types (but not spatial indexes)
- Data are stored in pages sorted by primary key
MyISAM specific functionalities
- fast execution of COUNT(*)s (when clauses WHERE, GROUP BY, or JOIN are not used)
- full text indexation (also available in most recent versions of InnoDB)
- smaller disk usage than InnoDB
- high table compression, but compressed tables are read-only
- spatial data types and indexes (R-tree)
Below is a brief explanation of each of these functionalities. Taking this into account, the DBA can take the right decision about the most adequate storage engine for the kind of usage and data stored in each table.
ACID stands for Atomicity, Consistency, Isolation, Durability. These are the required characteristics for a reliable transaction.
All the operations performed in an ACID transaction constitute a single atomic block, that can be validated with a COMMIT sentence, or rolled back with a ROLLBACK sentence, leaving the database in the same state as before.
Row level locking and table level locking
If several users try to access the same InnoDB table to perform changes, the row level locking allows all of these changes to be made concurrently, unless they try to modify the same row, in which case they are queued and executed sequentially.
If the table is MyISAM, the whole table is locked for each change, and all of them have to be made in sequence.
Foreign key constraints
Often, data in a table are related to data in other tables. Foreign key constraints ensure the integrity of these relationships. This referential integrity is implemented in InnoDB by means of foreign key constraints, while MyISAM does not implement this functionality.
- In an e-commerce application the database includes three tables “customers”, “products” and “orders”.
- The table “customers” has a field “customer_id” that uniquely identifies each customer and is the primary key for that table.
- The table “products” has a field “product_id” that uniquely identifies each product and is the primary key for that table.
- The table “orders” has a field “customer_id” and a field “product_id” together with other information (“quantity”, etc.)
For these data to be consistent, it should not be possible to insert a record in the “orders” table with values in the fields “customer_id” and “product_id” that do not exist in the correspondient fields in tables “customers” and “products”.
It should not be possible, either, to delete a customer or a product if they are being referenced by records in the table “orders”.
This is achieved by defining two foreign keys in the “orders” table:
CREATE TABLE orders (
order_id int NOT NULL primary key,
customer_id integer NOT NULL,
product_id integer not null,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(poduct_id)
- The referenced tables must also use the InnoDB engine to be able to create the foreign keys.
Recovery from crash
If the database service crashes due to a hardware fault or for some other reason, the MyISAM tables need to be recovered with a full repair of tables and indexes. If the tables hold a large amount of data, the time requiered to recover the service can be quite high.
On the other hand, InnoDB tables are automatically repaired by replaying the transactions recorded in the transaction log files.
InnoDB can use compressed tables, although the compression ratio achieved is not very high. Tables remain read-write, being possible to modify the content by means of INSERT, UPDATE and DELETE sentences.
The myisampack command can be used on MyISAM tables to achive a much higher compression, but compressed MyISAM tables become read-only.
Spatial data types
Columns in a MySQL table can be defined to hold geometrical data.
The spatial data types available are POINT, LINESTRING and POLYGON. There is also a generic GEOMETRY data type that can hold data of any of those geometrical data types.
There are also data types available to hold collections of geometrical data: MULTIPOINT, MULTILINESTRING, MULTIPOLYGON and GEOMETRYCOLLECTION.
Both InnoDB and MySQL give support for geomatrical data. The difference is that in MyISAM it is also possible to define indexes on columns of these data type, while in InnoDB this is not possible.
Full Text indexation
Since MySQL version 5.6, it is possible to create full text indexes in both InnoDB and MyISAM tables. For versions older than 5.6, this type of index is only supported on InnoDB tables.
- Innodb Storage Engine: http://dev.mysql.com/doc/refman/5.0/en/innodb-storage-engine.html
- MyISAM Storage Engine: http://dev.mysql.com/doc/refman/5.0/en/myisam-storage-engine.html