Reputation: 301
My table t1 has an int auto_increment PK (id) with values from 1 to about 40k.
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` int NOT NULL,
`col2` int NOT NULL,
...
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40124 DEFAULT CHARSET=latin1
Some code that clears and resets the data in the table is as follows:
TRUNCATE TABLE t1;
INSERT INTO t1 (col1, col2, ...)
SELECT ... FROM t2 WHERE ... ORDER BY ...;
The number of rows inserted in this way is typically between 40k and 41k.
t1.id
is not inserted in the select-insert.
Upon running
show create table t1;
it shows a new auto_increment value for the id of exactly 65536:
...
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=latin1
However, the values of id are still from 1 to 40123 or whatever the number of rows resulted from the select-insert.
But a new insert sets the id to 65536.
> select * from t1 order by id desc limit 10;
+-------+---------------+---------------+---
| id | col1 | col2 |...
+-------+---------------+---------------+---
| 65536 | 12345 | 123456 |...
| 40123 | 73627 | 736273 |...
| 40122 | 45678 | 456789 |...
Does mysql anticipate that since 40k is the approximate resultset size for the Select-Insert, it should use the nearest upper milestone size of 65536 ?
Or is it necessary to put an explicit statement after TRUNCATE ?
TRUNCATE TABLE t1;
ALTER TABLE t1 SET AUTO_INCREMENT=1;
INSERT INTO t1 (col1, col2, ...)
SELECT ... FROM t2 WHERE ... ORDER BY ...;
Upvotes: 1
Views: 208