MyISAM vs InnoDB for quick inserts and a composite unique key

Question

Context: I'm creating a multi-threaded application that will be inserting/updating rows very frequently.

Originally I had the following table:

#TABLE 1
CREATE TABLE `example` (
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `state`))
ENGINE = MyISAM;

However after doing some research I found that MySQL uses table-level locking for MyISAM tables permitting only one session to update those tables at a time (source). Not good for a multi-threaded application making frequent changes to the table.

As such, it was suggested that I switch from a composite primary key to an auto-generated primary key with a unique index for id/state. This would allow for quick inserts while still enforcing the unique combination of the id/state.

#TABLE 2
CREATE TABLE `example` (
  `key` BIGINT(20) NOT NULL,
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`key`),
  UNIQUE INDEX `ID_STATE` (`id` ASC, `state` ASC))
ENGINE = MyISAM;

InnoDB however avoids table locks and instead uses row-level locking (source) so I thought of switching over to the following:

#TABLE 3
CREATE TABLE `example` (
  `key` BIGINT(20) NOT NULL,
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`key`),
  UNIQUE INDEX `ID_STATE` (`id` ASC, `state` ASC))
ENGINE = InnoDB;

But after reading up about InnoDB, I discovered InnoDB organizes data using a clustered index and secondary indexes require multiple look ups. One for the secondary index and another for the primary key (source). As such I'm debating switching to the following:

#TABLE 4
CREATE TABLE `example` (
  `id` BIGINT(20) NOT NULL,
  `state` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `state`))
ENGINE = InnoDB;

I'm wondering if all my assumptions are correct:

MyISAM table locks the entire table for INSERTS, UPDATES, and DELETES permitting only one session to update those tables at a time
InnoDB handles INSERTS with composite primary keys quicker than MyISAM. This is because InnoDB, unlike MyISAM, does not lock the entire table to scan and reserve a new primary key.
When using InnoDB I should make a composite primary key rather than a composite unique index because a secondary index requires multiple look ups.
I should be using Table 4

You're right, I've updated it to better clarify what I meant. — Rawr, Jul 08 '16 at 00:08

score 3 · Accepted Answer · answered Jul 10 '16 at 23:26

3

1-yes, 2-yes, 3-yes, 4-yes.

Also...

Do you really need BIGINT? Won't 4 billion values in INT UNSIGNED suffice? (And save half the space.) Presumably id is the PK of some other table? If so, that table would need changing, too.
Can state be normalized? Or turned into an ENUM? Again saving space.

Item 3 is worse than mentioned because of the need to lock on two unique keys.

answered Jul 10 '16 at 23:26

Rick James

135,179
13
127
222

Can you clarify what you meant by "worse than mentioned?" Are you saying that creating a composite secondary index requires more than just two look ups? P.S. Good points all of them. I never thought about `BIGINT(10)` being 10 billion and `INT UNSIGNED` being 4 billion. – Rawr Jul 11 '16 at 02:03
`BIGINT` is a 64-bit number `+/- 8 billion billion`; the `(10)` says _nothing_. – Rick James Jul 11 '16 at 02:08

MyISAM vs InnoDB for quick inserts and a composite unique key

1 Answers1

Linked