CODEWITHSUNDEEP
mysql
Danny Liu
Danny Liu

Reputation: 48

Is composite key better than regular ID as key and unique index on several columns?

Is there any efficiency boost on using a composite key? In a lot of cases it seems to me that regular id column is not needed because each row in SQL is uniquely identified by several columns together. Unique index is applied on those columns. So my question is would be benefited from using that unique index as primary key and drop id.

Upvotes: 2

Views: 225

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562358

One benefit is that an InnoDB* table uses the primary key as a clustered index, that is, it stores the table in clustered index order, and this makes primary key lookups more efficient than a secondary key lookup (even in cases when the secondary index is a unique key). If you always look up rows by your secondary unique columns and not by the id pseudokey, then there's an advantage to using those columns as your primary key.

On the other hand, using a bulky set of columns as your primary key increases the size of other secondary indexes, because in InnoDB, each secondary index leaf contains the values of primary keys where that value occurs. If your primary key is three integers, a date, and a varchar(32), then that adds more bulk to the secondary indexes. How much depends on your data types and how many secondary indexes you have.

There are also some cases of unavoidable deadlocks that can occur when you have both an auto-increment primary key and a secondary unique key.

There's also an outstanding bug on unique keys that allows them to get duplicate values in a rare race condition.

* I assume you're using InnoDB, because no one should use MyISAM.

Upvotes: 2

Related Questions