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.)