MySql `id` column has a 'PRIMARY' and a 'UNIQUE' index, is this ideal?

Everything I can find on the internet explains the difference between these types of indexing, that is NOT my question.

When I create a new table in a database, I always create an id primary key as I'm sure most people do. I always make it PRIMARY KEY, NOT NULL, UNIQUE, AUTO_INCREMENT.

I'm recently getting big into adding custom indexes and I've noticed that my id field has these two index's (automatically created, for obvious reasons):

There is a PRIMARY index
There is also a UNIQUE index

My assumption is due to the PK flag (primary key) the 'PRIMARY' index was automatically generated, and due to the UNIQUE flag the 'UNIQUE' index was created.

I assume having two indexes on the same column is adding unnecessary overhead. My question is, is this true? Should I remove one of these index's? Or is it normal/ideal to have both of these index's created on my id column?

Solution

PRIMARY KEY(x), UNIQUE(x) -- Since a PRIMARY KEY is by definition (in MySQL) UNIQUE, the later index is redundant and should be dropped.

Also, in UNIQUE(x), INDEX(x) the INDEX(x) is redundant.

Redundant indexes take up extra space. (In a future version, they may even be disallowed -- that is how useless they are.)

Every table should (or even "must") have a PRIMARY KEY. It can either be an AUTO_INCREMENT, or some "natural" key. "Natural" in the sense that it is UNIQUE and never NULL. In a brief survey, I found that only 25% of tables had an AUTO_INCREMENT column.