CODEWITHSUNDEEP
mysqlsqlindexingclustered-index
Alex R
Alex R

Reputation: 11881

How to make an existing AutoIncrement index become the Clustered Index in MySQL?

This is a bit puzzling.

I have a unique non-clustered index I want to turn into the clustered index for that table.

I can't drop and recreate the index, because dropping the index on the AI column is illegal.

here's the current DDL

CREATE TABLE `RETS_STAGE1_QUEUE` (
  `LN_LIST_NUMBER` int(8) unsigned zerofill NOT NULL,
  `RECORDMODDATE` datetime NOT NULL,
  `syncTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `xmlText` varchar(64000) NOT NULL,
  `RETS_STAGE1_QUEUE_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `RETS_STAGE1_QUEUE_ID` (`RETS_STAGE1_QUEUE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=838053 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED

Upvotes: 0

Views: 103

Answers (1)

Arth
Arth

Reputation: 13110

The AUTO_INCREMENT column is already being used as the clustered index.

From the docs:

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

Upvotes: 1

Related Questions