Search code examples
mysqlinnodb

When should you choose to use InnoDB in MySQL?

I am rather confused by the hurt-mongering here.

I know how to do them, see below, but no idea why? What are they for?

create table orders (order_no int not null auto_increment, FK_cust_no int not null, 
foreign key(FK_cust_no) references customer(cust_no), primary key(order_no)) type=InnoDB;


create table orders (order_no int not null auto_increment, FK_cust_no int not null, 
foreign key(FK_cust_no) references customer(cust_no), primary key(order_no));
Solution

  • InnoDB is a storage engine in MySQL. There are quite a few of them, and they all have their pros and cons. InnoDB's greatest strengths are:

    • Support for transactions (giving you support for the ACID property).
    • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
    • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.