Posted on

InnoDB without PRIMARY KEY

Having an InnoDB table without a PRIMARY KEY is not good. Many have known this for years, but exact opinions as to why have differed. From observation, it was clear to me that it impacted performance.

InnoDB stores its row data in the leaf nodes of the primary key B+tree structure, that means that it can’t work without… so if you don’t specify a PK, it makes one up. Seems pretty innocuous and shouldn’t actually perform any worse than an auto-inc field. Except that in reality the performance can be much much worse. Annoying. Naturally we recommend clients to always have a PK (auto-inc, a composite of foreign keys, or if need be a natural key) but production systems cannot always be quickly changed, depending on the app code adding a column is not something you can just do at the DBA level.

Recently my good friend and former colleague Jeremy Cole, who has been delving into the depths of InnoDB, asked me if I had any open questions on the topic. So I mentioned the above, and after a brief look at the relevant code caught he was definitely interested in exploring the issue. The result is this blog post: http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/ for your enlightenment and enjoyment.

Now we know what goes on internally. And it’s clear that performance is negatively affected, and why. Useful.

Thanks Jeremy!

Posted on