Is there any performance reason to append an unused AUTO_INCREMENT column in MySQL 5.6? In my work environment, MySQL has some weird stuff with performance, and one of the other devs wanted me to research this situation before dropping a column that has the AUTO_INCREMENT attribute.

Scenario

We have a table that has a FOREIGN KEY that is used to INDEX into it.  It also currently has a column with the AUTO_INCREMENT attribute that is defined as a KEY, but that KEY is completely unused.

Question

Just wanting to confirm that MySQL is not doing something weird, and if having no column with the AUTO_INCREMENT attribute on a table is going to slow things down.

Consideration

I would like to drop the unused AUTO_INCREMENTed column, and it seems highly likely this action can only speed things up for us.
Yes and no.

(The following applies to all versions, but only to ENGINE=InnoDB.)

An InnoDB table always has a PRIMARY KEY:


You could specify one; else
A UNIQUE key with non-null column(s); else
A hidden 6-byte number.  (This number does not otherwise exist.)


If you remove your PK, then option 2 or 3 will kick in.  If option 3 kicks in, each row will be 2 bytes bigger -- assuming your id is a 4-byte INT now.  This is a tiny performance hit.

The data in the table is ordered according to the PRIMARY KEY:


With AUTO_INCREMENT or the hidden key, new inserts are being "appended" to the table;
With some other UNIQUE or PRIMARY key, the rows are inserted 'where they belong'.  This is a tiny bit less efficient.


When you need to do maintenance on the table, you often need a unique way to identify each row.  For this reason, alone, I advocate having a visible, explicit PRIMARY KEY.

Which is a "better" PRIMARY KEY:


An AUTO_INCREMENT?
A "natural" key made up of one or more columns that are naturally unique?


Answer:  It depends.  Since that is not the topic of your question, I will defer the elaboration to another Q&amp;A.  Also, since the PK is an index, there could be performance considerations for indexing.