Mysql – Does it makes sense to index a primary key if it is not used

foreign keyindexMySQLperformanceperformance-tuningprimary-key

I have a database with many tables which can be joined by indexed keys (primary and foreign keys). Some tables are transaction tables which have an autoincremental primary key just to define a ID for each transaction.

To use this database and process the data (cleansing, adding new columns) I copy the database. The processes on the new database (updates, inserts) are quite complex. I index columns where it makes sense but I don't want to index columns which I don't need due to reason of performance.

Now, I come to the point: there are several huge transactions tables which have been defined from the original database as primary key but which are not used as key for joining. I would like to remove these primary keys (not the column but just: ALTER TABLE trans DROP PRIMARY KEY) to avoid too many indexed columns. Is there any reason not to remove index on a primary key?

If this question is dependent on RDBMS: I use MySQL/MariaDB.

Best Answer

In MySQL, if you have an auto_increment column, it MUST also be a key/index (either primary or a unique or a non-unique one, so any index really) where auto increment column is the first in the index/key definition.

For any InnoDB tables that do not have a PRIMARY or UNIQUE NOT NULL key defined, MySQL will implicitly create (a 6-byte) internal key under the hood and use it as the clustered index.

This clustered key (the PRIMARY or the first UNIQUE NOT NULL or the 6-byte internal) is always included in ALL secondary indexes for InnoDB tables in MySQL.

In your situation, dropping the PRIMARY key will only result in MySQL creating an implicit one anyway (unless you define another PRIMARY or UNIQUE NOT NULL key explicitly). This also applies only to InnoDB tables.

Related Question