InnoDB

MyISAM and InnoDB are two most commonly used storage engines of MySQL database.

However, MyISAM is the default storage engine chosen by MySQL database, when creating a new table.

The default engine is MyISAM which is very fast but does not support higher level functionality such as foreign keys or transactions.

# mysql> SHOW ENGINES;

[*] InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
[*] InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
[*] InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.

INNODB FEATURES

1) Transactions

InnoDB tables support transactions. A transaction allows multiple SQL commands to be treated as a single and reliable unit.

Consider a banking application where you are transferring money from one account to another. The transaction would only be committed if both accounts were altered successfully. If anything failed, the database would be rolled-back to a previous state.

If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

What is a transaction?

The classic example is from the banking environment. An amount of money is taken from one person’s account, and put into another, for example as follows, a 500-unit transaction:

UPDATE account1 SET balance=balance-500;
UPDATE account1 SET balance=balance+500;

Both queries must run, or neither must run. You cannot have the money being transferred out of one person’s account, and then ‘disappearing’ if for some reason the second query fails.

Both these queries form one transaction. A transaction is simply a number of individual queries that are grouped together.

Down to work: InnoDB Transactions

Transactions are wrapped in BEGIN and COMMIT statements. Let’s create a sample InnoDB table, and see how transactions work:

mysql> CREATE TABLE t (f INT) TYPE=InnoDB;

Now let’s begin a transaction, and insert a record:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t(f) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
+——+
1 row in set (0.02 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t;
Empty set (0.00 sec)

Without a COMMIT statement, the insert was not permanent, and was reversed with the ROLLBACK. Note that the added record was visible during the transaction from the same connection that added it.

2) Foreign Key

A foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables.

A foreign key establishes a relationship between two tables.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table.

To do this, we will place a foreign key in the ORDER table and have it relate to the primary key of the CUSTOMER table.

3) Row-level locking

InnoDB uses row-level rather than table-level locking. If a row is being inserted, updated or deleted, only changes to the same row are held up until that request has completed. Tables that receive more updates than selects may be faster with InnoDB.

This means that mass inserts and updates, MyISAM table access can become significantly slowed down as MySQL has to wait for the database engine to release the lock on the table caused by the insert or update.

InnoDB defaults to locking only the rows which are affected by the insert or update query, meaning the rest of the table can still be accessed without waiting for a lock release.

Advantages of row-level locking:

[*] Fewer lock conflicts when accessing different rows in many threads.
[*] Fewer changes for rollbacks.
[*] Makes it possible to lock a single row a long time.

use a row level lock if you are only hitting a row or two. If your code hits many or unknown rows, stick with table lock.

4) Consistent reads

Let’s try looking from a different connection. For this exercise, open two connections to the database.

Connection 1:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (f) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
+——+
1 row in set (0.00 sec)

Connection 2:

mysql> SELECT * FROM t;
Empty set (0.02 sec)

The important point is that running the same query from different connections (one within the middle of a transaction, the other from without) produces different results. Now, commit the transaction from the first connection, and run the query again from connection 2.

Connection 1:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Connection 2:
mysql> SELECT * FROM t;
+——+
| f |
+——+
| 1 |
+——+
1 row in set (0.00 sec)

This behavior is called consistent reading. Any select returns a result up until the most recently completed transaction, with the exception of the connection doing the updating, as we saw above. By default, MySQL InnoDB tables perform consistent reads.

The disadvantages of InnoDB tables

1) Increased complexity
Creating an InnoDB table is no more complex than MyISAM.

2) No full-text search
InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.

3) Slower performance
If your application is primarily selecting data and performance is a priority, MyISAM tables will normally be faster and use fewer system resources.