Home / server / Questions / 66598
Accepted
vartec
Asked: 2009-09-19 01:14:33 +0800 CST

Will dropping AUTO_INCREMENT rebuild table in MySQL?

  • 772

I've got table with following schema:

CREATE TABLE `tblsomething` (
  `something_id` int(11) NOT NULL AUTO_INCREMENT,
   …
 PRIMARY KEY (`something_id`)
) ENGINE=InnoDB AUTO_INCREMENT=144620955

I need to drop the auto increment. So I'm thinking to do:

ALTER TABLE tblsomething MODIFY something_id int NOT NULL;

But this part of MySQL doc worries me:

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

So is dropping auto increment actually one of these cases? Will it lock up my table?

mysql

2 Answers

  1. Certain ALTER TABLE statements will always produce a dreaded rebuild as your describe.

    In the event of removing AUTO_INCREMENT fields the only way to prevent this is some unsupported hackery. This involves modifying a copy of the table's .frm file. It works because schema information is held separately from the data and index information and the modifications don't produce any inconsistencies between the three.

    You can find a discussion about it at mysqlperformanceblog.org and in the High Performance MySQL book. It's important to stress that it is unsupported though. I'd recommend that you test both methods (plain ALTER TABLE and .frm editing) on a copy of the data first. See how long both processes take and check the consistency after.

    Edit: Sorry, I re-read what was written and ALTER COLUMN doesn't apply to what you're doing. I've updated the text above.

    • 2
  2. Best Answer

    Removing (or setting) AUTO_INCREMENT flag does not rebuild the entire table. You can easily test that using a table with a large number of rows (setting that flag is almost instantaneous).

    Update: This was probably true long time ago (with MyISAM), but looks it is no longer the case.

    • 1