Moving all rows from one table to another, deleting from original table but retaining auto_increment

Question

I'm trying to correctly move all rows from one table to another and then delete them from the original table without resetting the auto_increment value.

The following seems to reset the auto_increment value... any idea what I am doing wrong?

$sql1   = "INSERT INTO archive SELECT * FROM main";
$result1 = mysqli_query($sqlsession, $sql1) or die(mysqli_error());

$sql2   = "truncate main";
$result2 = mysqli_query($sqlsession, $sql2) or die(mysqli_error());

truncate table xxx does that behavior, kinda nice actually. But not in your case — Drew, Sep 19 '15 at 05:10
truncate deallocates data pages, is faster, then the value of auto_inc can be set with alter table. It's an option at least. Delete is logged. Slow. Got 20 rows, go with delete. 2 million, not a chance — Drew, Sep 19 '15 at 05:16

score 3 · Accepted Answer · answered Sep 19 '15 at 05:10

3

DELETE FROM main

instead of truncate

answered Sep 19 '15 at 05:10

Your Common Sense

156,878
40
214
345

score 1 · Answer 2 · edited May 23 '17 at 10:27

For speed, deallocation of data page size, I would steer toward truncate table always. Despite having to set the auto_inc back to what you want in your special case. So it's a one two punch like below.

truncate table myTable;
alter table myTable AUTO_INCREMENT = 200001;

Truncate Table manual page. Several stackoverflow posts have mentioned it. Here is one.

If I had few rows, I would go with delete from myTable. With huge sets, never, and would use the above.

The existence of Foreign Key constraints factor into the likelihood of truncate or delete success. So consider that.

Moving all rows from one table to another, deleting from original table but retaining auto_increment

2 Answers2