Is a unique index required for a sequential (autoincrementing) id?

Question

If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity?

I'm working on reindexing a particular MySQL table to speed up certain very common WHERE clauses. The table is from the WordPress CMS. Its out-of-the-distro definition, simplified, is this.

CREATE TABLE wp_postmeta (
    meta_id BIGINT UNSIGNED AUTO_INCREMENT,
    post_id BIGINT UNSIGNED,
    meta_key VARCHAR(255),
    meta_value LONGTEXT,
    PRIMARY KEY (meta_id),
    INDEX post_id (post_id),
    INDEX meta_key (meta_key)
);

MariaDB / MySQL (InnoDB) now uses clustered indexes, so I've changed the PK to this:

    PRIMARY KEY (post_id, meta_key, meta_id)

That means WHERE filters such as post_id = 42 AND meta_key = 'value' can be satisfied using the clustered index and LONGTEXT fetching. It's proven to solve my users' performance problem.

The question is this: does this reindexed table also need a unique index on the autoincrementing ID column to keep working correctly long term? Or can I omit that index to save a little space and UPDATE / INSERT performance?

    UNIQUE INDEX meta_id (meta_id),

(I'm aware there might be far better table designs than this one. But my present assignment doesn't allow me to change the table, just the indexes.)

Answer 1

Turns out this was a dumb question. The DBMS won't allow an autoincrementing column without an index. It throws Error 1075 upon attempts to do this.

Because of other filter patterns not mentioned in the question I'm going with these indexes.

PRIMARY KEY (post_id, meta_key, meta_id)
UNIQUE INDEX meta_id (meta_id)
INDEX meta_key (meta_key, meta_value(32), post_id)
INDEX meta_value (meta_value(32))

The prefix indexes are on the LONGTEXT column. In meta_key if I could do

INDEX meta_key (meta_key, meta_value(32)) INCLUDE(post_id)

I would, but this RDBMS doesn't INCLUDE() things in indexes.

Answer 2

You want to change the PRIMARY KEY from

PRIMARY KEY (meta_id)

to

PRIMARY KEY (post_id, meta_key, meta_id)

This is allowed in InnoDB engine but it requires that you have an index (not necessarily UNIQUE on (meta_id) or an index with more columns and meta_id as the first column.

---

So, to answer the last question:

Or can I omit that index to save a little space and UPDATE / INSERT performance?

No, you'll still need an index on meta_id and (unique or not) the extra space.

---

Regrading the question about integrity:

If a sequential AUTO_INCREMENT column is not also the primary key of a MySQL table, does it have to have its own UNIQUE index to ensure long-term data integrity?

This really depends on whether uniqueness is required for (meta_id). You'll have to check whether any other WordPress procedure and your application depends on (meta_id) being unique.

If not, you don't need to have the index as UNIQUE.

As a precaution though, I'd suggest you keep it as UNIQUE. You never know if in a few years, you or the next developer decides to change the PK back to the default or you add a plugin that breaks with non-unique meta_id values. Or change to a different platform and having these unique keeps migration simpler.

---

Going back to saving space, you can consider that with the new PK having 3 columns, any other secondary index (unique or not) has all these 3 columns included as well.

So your indexes:

PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id),
INDEX post_id (post_id),
INDEX meta_key (meta_key)

are really (under the hood):

PRIMARY KEY (post_id, meta_key, meta_id),
UNIQUE INDEX (meta_id) INCLUDE (post_id, meta_key),
INDEX post_id (post_id) INCLUDE (meta_key, meta_id),
INDEX meta_key (meta_key) INCLUDE (post_id, meta_id)

The post_id index is therefore redundant and you can drop it without any loss. Any query that would previously use the post_id, can now use the PK instead.

Re: length of PK. If there is exactly one secondary index, then the disk space required when having PRIMARY KEY(id), INDEX(foo, bar) is almost identical to the space for PRIMARY KEY(foo, bar, id), INDEX(id).

If there is more than one secondary index, then a bigger PK leads to more disk space.

Answer 3

Short answer: in InnoDB, the auto-increment column must be the leftmost column of some index, either the primary key (clustered) index, or else some secondary index. It doesn't have to be a unique key, any B-tree index will do.

The following is not valid...

PRIMARY KEY (post_id, meta_key, meta_id)

unless another index (aka key) is defined with meta_id as the leftmost column.

KEY (meta_id)

Since an auto-increment column is naturally unique by itself, it's uncommon to make a primary or unique key of the auto-increment column along with other columns.

Answer 4

INDEX(meta_id) 

Is the minimum needed to satisfy MySQL. (As mentioned in other Answers.)

However, that allows the user to explicitly add a duplicate value for meta_id. Presumably WP never does this 'dumb' thing.

UNIQUE(meta_id)

Slows down INSERTs slightly -- because the Insert won't return to the client until the uniqueness has been checked. With a plain INDEX(meta_id), the Insert puts the update for that index into the "change buffer" for later storing into the BTree for that secondary index.

Another difference between UNIQUE and INDEX comes when SELECTing by a specific value for meta_id. With INDEX, the query keeps reading rows, looking for more rows with the same meta_id. On the other hand, UNIQUE can stop after hitting the first occurrence.

Neither of those "differences" is worth worrying about when worrying about performance. I am merely mentioning them for completeness.

Answer 5

Depends what you mean by "keep working correctly long term"? A unique index will only do its job of preventing duplicate values from being entered, regardless if the field has an auto increment specification or not. But that doesn't stop someone from manually inserting an out of sequence value that is unique into the meta_id field.